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

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

### Step 1: Insert Local Time Zone and Time

• Â Enter the following formula to display local time in cell D4.
`=NOW()`
Â […..] 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.

`=\$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: Refer Countries by Time Zones

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

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

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

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