The main formulas used in the file are:
=QUERY(‘Time Zones’!F:G, ” select * where F = ‘”&’Time Converter’!A13&”‘ ” )
- We are searching on the [Time Zones] TAB for the country entered in the cell A13 in our case
- The formula will return all the Cities linked to that Country on the [Time Zones] TAB
- This result will be displayed on the adjacent column to where the formula was entered
- For convenience, that column showing the results can be a “Named Range” – it will make it much easy to use it in formulas and clearly more easy to debug
- In my case, there are 5 TARGET Country/City combinations so I used this formula 5 times (the Columns A, C, E, G, I, K of the calculation tab called [City])
=$C$13–VLOOKUP(CONCATENATE($A$13,“/”,$B$13),‘Time Zones’!H:M,5,False)/24–VLOOKUP(CONCATENATE($A$13,“/”,$B$13),‘Time Zones’!H:M,6,False)/1440
- C13 is the cell where we enter the reference time, the one that will be used to calculate the time on the other Cities.
- The first VLOOKUP is calling the number of hours between the 2 locations and the second VLOOKUP is calling the number of minutes between the 2 time zones (yes, there are some time zones in increments of 30 and even 15 minutes: Adelaide or Eucla in Australia for example)