I live and work in the UK but I have many customers based around the East Coast of the USA. In terms of their timezone, it is 5 hours behind the UK. However, because the UK and the USA don’t put their clocks forward and back at the same time, sometimes they are 4 hours behind the UK.
The list below shows the time difference for 2014 and 2015:
- 01 January 2014 – 25 October 2014 : 5 hours
- 26 October 2014 – 01 November 2014: 4 hours
- 02 November 2014 – 07 March 2015 : 5 hours
- 08 March 2015 – 28 March 2015 : 4 hours
- 29 March 2015 – 24 October 2015 : 5 hours
- 25 October 2015 – 31 October 2015 : 4 hours
- 1 November 2015 : 5 hours
I created an Excel file where I can type in a date and a UK time and a formula will return the corresponding time in the USA:
Column A contains a list of dates. These are the dates when the time difference changes from 5 to 4 and back again.
Column B contains the time difference in hours on that date. This value is in force until the next change date.
To calculate the US time on any given date, I type the date into E1 and a UK time into E2. The formula that calculates the corresponding time in the USA is in E3.
Explanation
- An hour is 1/24th of a day
- To calculate the time in the USA when there is 5 hours difference, the formula would be =E2+(5/24)
- To calculate the time in the USA when there is 4 hours difference, the formula would be =E2+(4/24)
- The reason I’m adding and not subtracting is because I’ve used a negative value in column B (to indicate that the USA is “behind” the UK). If I’d used a positive value, the formula would be =E2-(5/24) or +E2-(4/24)
Rather than creating 2 formulas – one for 5 hours difference and one for 4 hours difference, I’ve used a VLOOKUP function to automatically generate the 4 or 5 depending on the date specified in E1.
VLOOKUP(E1,A1:B7,2,TRUE)
Excel looks in E1
Excel then looks down the range A1:A7 for a matching value
In the screenshot above, it finds a match (in A4), so it stops and moves across to column B which is the 2nd column of the range A1:B7 (that’s what the 2 indicates in the function). The value in B4 is -4
However, what if a match cannot be found?
Excel looks in E1
Excel then looks down the range A1:A7 for a matching value
In the screenshot above, it does not find a match
However, the 4th argument of the VLookup function is TRUE which means “if you can’t find an exact match, find an approximate match”. Approximate is defined as the next largest value that is less than the lookup_value.
In other words, if the date in E1 is 25th october 2014, the VLOOKUP function returns 5 as the answer. This is because 25th October 2014 is after 1st January 2014 but before 26th October 2014.
IMPORTANT: When using the TRUE argument, the items in the first column of the lookup table (A1:A7 in this case), must be in ascending order.
The VLookup is used to generate the 4 or 5 but the full formula in E3 is:
=E2+(VLOOKUP(E1,A1:B7,2,TRUE)/24)