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.


Download Excel Workbook

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


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.

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


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.

=$D$4-$D$3/24

 […..] 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 Convert UTC to EST in Excel (3 Easy Ways)


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 Convert Time Zones in Excel (3 Ways)


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.

=$D$5+B8/24
[…..] 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 Create World Time Zone Clock in Excel (2 Easy Methods)


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.

=IF(E9="NO",($D$5+B9/24),($D$5+$D$6/24+B9/24))
[…..] 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: Convert Time Zone with Daylight Savings in Excel (2 Cases)


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


Conclusion

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.

Do check out our awesome website, Exceldemy, to find interesting articles on Excel.


Related Articles

Maruf Islam
Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo