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()
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.
Read More: How to Calculate Due Date with Formula in Excel
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.
Download Excel Workbook
Download the dataset and use the Practice Sheet.
Related Articles
<< Go Back to Time Zone in Excel | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
