Making a List of Countries by Time Zone in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

Coordinated Universal Time, or UTC, is the primary standard that regulates clocks and time worldwide. Therefore, creating a list of countries by time zone in Excel is often required. The Excel formula constituted by Arithmetic Operators easily converts local time into country-wise local time depending on time zones.

Users may want a list of countries by time zone, as depicted in the latter picture.

List of Countries by Time Zone Excel

Go through the following steps to create a list of countries by time zone in Excel.

Step-by-Step Procedures to Create a List of Countries by Time Zone in Excel

Displaying time in the proper format is essential whenever users deal with time. To display time according to time zones after conversion, preformat the cells from the Format Cells window.

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

Time Format

Step 1: Inserting Local Time Zone and Time

Prior to generating a list of countries by time zone, users need to know their current time zone and current time. Let’s say users are in the +6.00 UTC time zone.

Insert the following formula to display local time in D4 cell.

 […..] the NOW() function returns the current date and time.

Read More: How to Copy Same Date in Excel

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

Now, users have to find ∓0.00 UTC time using Arithmetic Operators. It’s because the ∓0.00 UTC time will be used as the base value in creating the county’s list by time zone.

Type the following formula in D5 cell 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: Referring Countries by Time Zones

Afterward, compile a single country name for each time zone as shown in the below picture. There are a couple of countries that fall within the same time zone. Users can include all of them or only the selected ones, depending on their requirements. For simplicity, the used dataset contains only a handful of countries.

List of Referring Countries by Time Zone in Excel

Read More: How to Add Time in Excel Automatically

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

After inserting countries by time zones, users need to find the current times to complete the list.

Write the below 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: Considering DST Along with Time Zone

Some countries may include Daylight Saving Time in their local time. Therefore, users need to add Daylight Saving Time (DST) to their time zone’s local time calculations.

➤ Insert an adjacent column mentioning the DST status as YES or NO. Then use the latter 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

We’ve added a Practice Sheet to try out the steps in the Dataset. Download the dataset and practice with it.

Practice Sheet-List of Countries by Time Zone Excel

Download Excel Workbook

Download the dataset and use the Practice Sheet to try the steps.


This article discusses the step-by-step procedures to create a list of countries by time zone in Excel. The list may contain the time zone-wise current time or other components such as sub-time zones. However, in this article, only the local time and the preferable time format are discussed. We hope the steps help you achieve what you are looking for.

Related Articles

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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