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$13VLOOKUP(CONCATENATE($A$13,“/”,$B$13),‘Time Zones’!H:M,5,False)/24VLOOKUP(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!