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.