# 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.

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.

### 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.

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.

`=\$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 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.

### 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.

### 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.

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.

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

## 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.

## 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 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

Advanced Excel Exercises with Solutions PDF