CASE STUDY 3
Google Sheets - Time Zone Calculator
How to build a Google Sheets - time zone calculator
Google Sheets can do miracles but sometimes you need to use some tricks to make your life easy when building your file.
The main problem I see is to properly accommodate the countries covering multiple time zones. If you do not need to cover such an area, the problem is very simple. A VLOOKUP on a table with all the places and their time zones and a formula to cover those time zones differences that will add or subtract from your reference time.
Registered members will SEE MORE and will get the link to the Google Sheets - Time Zone Calculator free file.
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)
This content is locked
Login To Unlock The Content!
