We have city names and their respective time differences from the current time in a sheet. We’ll use this dataset to make a time zone clock.
Method 1 – Merge IF and TIME Functions to Create a World Time Zone Clock
Steps:
- Select cell F5 and insert the following formula:
=IF(C9>0,$E$4+TIME(C9,D9,E9),$E$4-TIME(ABS(C9),D9,E9))
- Press Enter on your keyboard. The function will calculate the current time for the city in row 5.
- AutoFill the function to the rest of the cells in column F.
- Select cell G5 to calculate the date of the world time zone.
- Insert the following formula in the cell.
=IF(C9>0,$E$5+TIME(C9,D9,E9),$E$5-TIME(ABS(C9),D9,E9))
- Press Enter on your keyboard. This is a similar formula, but the G column is formatted as a date (like the E5 cell) without hours so it just returns the date.
- AutoFill the function to the rest of the cells in column G.
Method 2 – Combine MOD and TIME Functions to Create a World Time Zone Clock in Excel
Steps:
- We modified the dataset a bit to include a starting city as the benchmark.
- Select cell H5 to calculate the time of the world time zone.
- Use the following formula.
=MOD($C$6+TIME(E6,F6,G6),1)
- Press Enter on your keyboard.
- AutoFill the rest of the column.
Things to Remember
➜ If the referenced cell doesn’t have a value, the #N/A! error happens in Excel.
➜ The #VALUE! error occurs when any of the given inputs are non-numeric.
Download the Practice Workbook
Related Articles
- How to Enter Time in Excel
- How to Add Military Time in Excel
- How to Copy Same Date in Excel
- How to Use IF Formula with Dates
- How to Calculate Due Date with Formula in Excel
<< Go Back to Time Zone in Excel | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!