Maruf Islam
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.
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.
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()
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
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.
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
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)) 
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.
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.
