Calculate time between time zones

This article demonstrates how to build a time zone chart and calculate times different than your time zone.

The worksheet below lets you enter cities and their time difference. Excel calculates the corresponding local times in E5:E8. Press F9 to refresh the time and date in cell E3.

1. How to calculate time zone differences

This worksheet lets you enter cities and their time difference. Excel calculates the corresponding local times in E5:E8. Press F9 to refresh the time and date in cell E3.

Formula in cell E3:

Formula in cell E5: =$E$3+C5/24 Copy this cell and paste to E6:E8. There are both absolute and relative cell references in this formula.

Did you know that Excel stores date and time as a number? 16 June 2015 is stored as 42171. 16 June 2015 12:00 is 42171.5. Time is the decimal part of the number (0.5) and the date is 42171.

You can verify that this is true, type a date in a cell. Select the cell and press CTRL + 1. Change the formatting to General.

To add 7 hours to a datetime value you need to divide the hours by 24. 7/24 + 42171 = 42171.02916667. That explains the formula in cell E5: =$E$3+C5/24

Another example, 24 hours (1 day) is equal to 1. 42171 + 24 hours is 42171 + 24/24 = 42172.

Back to top

2. Find the time difference

How do you know the time difference between where you live and another city/country? You don't, some countries use daylight saving time (summertime) or have changed their time zone. It can be really confusing.

Go to https://www.timeanddate.com/worldclock/ and search for a city, it is the only way.

Type the time difference in column C, Excel calculates the time for you in column E.

Back to top

3. How to create a time zone chart

This chart shows you the world divided into 24 time zones, in a perfect world this could be useful. Unfortunately, it is pretty much useless.

Don't use this chart to find the time difference between the two cities. It is not accurate, some countries have changed their time zone and use daylight saving time.

I have included this chart in this post only to show you how I built it.

Back to top

3.1 How I built the chart

3.1.1 Values and formulas

1. Go to sheet2
2. Type these values in columns B and C, see pic below.
3. Type this formula in cell D2: =IF(B2=Sheet1!$D$12, 1, 0)
4. Copy cell D2 and paste to D3:D26

Back to top

3.1.2 Insert a Drop-down List

1. Go to sheet1, select cell D12
2. Go to tab "Data"
3. Press with left mouse button on the "Data Validation" button
4. Select List
5. Enter these numbers: -12, -11, -10, -9, -8, -7, -6, -5, -4, -3, -2, -1, 0, +1, +2, +3, +4, +5, +6, +7, +8, +9, +10, +11, +12, +13, +14
   
6. Press with left mouse button on OK.

Back to top

3.1.3 Insert a column Chart

1. Go to sheet2.
2. Select cell range B2:C26.
3. Go to tab "Insert" on the ribbon.
4. Press with left mouse button on the "Column chart" button.
   
5. Press with right mouse button on on the chart and press with left mouse button on "Select Data...".
6. Press with left mouse button on the "Edit" button below Horizontal (Category) Axis Labels.
7. Select cell range B2:B26.
8. Press with left mouse button on OK.
9. Select "Series1".
10. Press with left mouse button on the "Edit" button.
11. Select cell range C2:C26 as series values.
    
12. Press with left mouse button on OK.
13. Select Series2.
14. Press with left mouse button on "Remove" button.
15. Press with left mouse button on OK.

Back to top

3.1.4 Adjust and remove vertical axis

1. Press with right mouse button on on the vertical axis.
2. Press with left mouse button on "Format Axis...".
3. Change "Maximum Value" to "Fixed" and type 1.
4. Press with left mouse button on "Close" button.
5. Select "Vertical Axis" and press Delete.

Back to top

3.1.5 Remove legend and major horizontal gridlines

1. Select "Legend".
2. Delete "Legend".
3. Select major horizontal gridlines.
4. Delete.

Back to top

3.1.6 Add vertical grid lines

1. Select chart.
2. Go to tab "Layout" on the ribbon.
3. Press with left mouse button on "Gridlines" button.
4. Press with left mouse button on "Primary Vertical Gridlines".
5. Press with left mouse button on "Major Gridlines".

Back to top

3.1.7 Add map to chart background

I found a world map at wikimedia commons. Copy picture (Ctrl + C)

1. Press with right mouse button on on plot area.
2. Press with left mouse button on "Format Plot Area...".
3. Press with left mouse button on "Picture or texture fill".
   
4. Press with left mouse button on "Clipboard" button.
5. Press with left mouse button on Close button.

Back to top

3.1.8 Adjust column gap width and transparency

1. Press with right mouse button on on the column.
2. Press with left mouse button on "Format Data Series...".
3. Change gap width to 0%.
   
4. Go to the "Fill" category.
5. Press with left mouse button on "Solid fill".
6. Change transparency to 50%.
   
7. Press with left mouse button on the Close button.

Back to top

Get the Excel file

Calculate-time-between-time-zonesv2.xlsx

Back to top

Date and time category

Create a date range [Formula]
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

Count unique distinct values that meet multiple criteria
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

Formula for matching a date within a date range
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

Excel categories

Comments (3)