World Clock Conversion in Excel
By setting up intelligent tables, Excel can generate accurate time zone information. During the design of the World Cup in Excel dashboard, I found a problem which was quite perplexing. I wanted to change the time zone automatically and have the appropriate time displayed based on the country/region selected. The program worked fine while I was adding numbers to the time but when it came to reducing the time Excel produced and error. When I took time off of a starting point by just saying something like
=17:00 - 7
the result was something like this
################
when I wanted the data to display 10:00 am (5pm - 7 hours).
In order to get around this there is a simple solution. You simply have to divide the hours which are being taken off by 24. So the following will work
=17:00 - 7/24
With this information we have the tools to make a world time clock schedule in Excel. I will keep the model small as there are hundreds of time zones and I will restrict this to a small amount so I can show the concept of a world clock conversion in Excel.
The above diagram show the local time and the time where you are as indicated by the drop down in yellow. Note the time change is negative and the adjusted time (Your time column) reflects this. The table on the right is a sample of time zones.
I have attached an Excel file which shows how this World Clock time zone would work. The Excel file shows an extensive list of countries.
