How to make a List of Countries by Time Zone in Excel (with Easy Steps)

In the dataset below, we have two columns: Time Zone and Country.

List of Countries by Time Zone Excel

  • Click the Number Format icon (Home > Number section) or CTRL+1 to display the Format Cells window.

Time Format

Step 1: Insert Local Time Zone and Time

  •  Enter the following formula to display local time in cell D4.
 […..] the NOW() function returns the current date and time.

Read More: How to Copy Same Date in Excel

Step 2: Find Coordinated Universal Time (∓0.00 UTC)

  • Enter the following formula in cell D5 to calculate the ∓0.00 UTC time zone’s time.


 […..] the formula takes local time D4 and adjusts the time according to the time zone. Dividing the Time Zone (i.e., D3) by 24 returns the hours and minutes to be added or subtracted.

Read More: How to Use IF Formula with Dates

Step 3: Refer Countries by Time Zones

Use a single country name for each time zone, as shown in the picture below.

List of Referring Countries by Time Zone in Excel

Step 4: Create a List of Countries by Time Zone in Excel

  • Enter the following formula in the adjacent cells to calculate the current times of different time zones.
[…..] in the formula D5 is the ∓0.00 UTC time and B8 is the Time Zone. Dividing the Time Zone by 24 returns the variable time that differs depending on the time zone.

Creating a List of Countries by Time Zone in Excel

Read More: How to Calculate Due Date with Formula in Excel

Step 5: Consider DST Along with Time Zone

  • Enter an adjacent column for the DST status as YES or NO.
  • Enter the following formula in any adjacent cells:
[…..] In the formula, the IF function takes DST status as logical_test. Then executes $D$5+B9/24 upon satisfying the test; otherwise, DST hour gets added.

Daylight Saving Time

Read More:  How to Create World Time Zone Clock in Excel

Practice Section

Download the Practice Sheet to try out the steps in the Dataset.

Practice Sheet-List of Countries by Time Zone Excel

Download Excel Workbook

Download the dataset and use the Practice Sheet.

Related Articles

<< Go Back to Time Zone in Excel | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF