Md. Abdur Rahim Rasel
Sometimes you may need to create a world time zone clock in Excel for the convenience of your work. To gain a clear grasp of this topic, you have to know about some Excel functions. This article will provide you with two quick methods to create a world time zone clock in Excel effectively with appropriate illustrations.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
2 Suitable Ways to Create World Time Zone Clock in Excel
Let’s get introduced to our dataset first. I have the Name of some cities, and Time differences on my sheet and I’ll use the IF and TIME functions in Excel to create a world time zone clock using this dataset.
1. Merge IF and TIME Functions to Create World Time Zone Clock
In this method, we will apply the IF and TIME functions to create a world time zone clock in Excel. The world time zone clock is dynamic. It changes time automatically. From our dataset, we can easily do that. Firstly, we will calculate the current time, then we will calculate the current date of the world time zone clock using the IF and TIME functions. Let’s follow the instructions below to learn!
Step 1:
- First of all, select cell F5 to calculate the time of the world time zone.
- After that, write down the below functions in that cell.
=IF(C9>0,$E$4+TIME(C9,D9,E9),$E$4-TIME(ABS(C9),D9,E9))- Hence, simply, press Enter on your keyboard. As a result, you will be able to get the return of the IF and TIME functions and the return is 10:50:42 PM.
- Further, AutoFill the IF and TIME functions to the rest of the cells in column F which has been given in the screenshot.
Step 2:
- First of all, select cell G5 to calculate the date of the world time zone.
- After that, write down the below functions in that cell.
=IF(C9>0,$E$5+TIME(C9,D9,E9),$E$5-TIME(ABS(C9),D9,E9))- Hence, simply, press Enter on your keyboard. As a result, you will be able to get the return of the IF and TIME functions and the return is Monday, September 12, 2022.
- Finally, AutoFill the IF and TIME functions to the rest of the cells in column G which has been given in the screenshot.
Read More: How to Convert Time Zones in Excel (3 Ways)
2. Combine MOD and TIME Functions to Create World Time Zone Clock in Excel
Now, we will apply the MOD and TIME functions to create a world time zone clock in Excel. Though the world time zone clock changes time automatically, the MOD function can not do that. From our dataset, we can easily do that. Let’s follow the instructions below to learn!
Steps:
- First of all, select cell H5 to calculate the time of the world time zone.
- After that, write down the below functions in that cell.
=MOD($C$6+TIME(E6,F6,G6),1)- Hence, simply, press Enter on your keyboard. As a result, you will be able to get the return of the MOD and TIME functions and the return is 8:20:30 PM.
- Further, AutoFill the MOD and TIME functions to the rest of the cells in column H which has been given in the screenshot.
Read More: Convert Time Zone with Daylight Savings in Excel (2 Cases)
Things to Remember
➜ While a value can not found in the referenced cell, the #N/A! error happens in Excel.
➜ The #VALUE! error occurs when any of the given inputs are non-numeric.
Conclusion
I hope all of the suitable methods mentioned above to calculate the world time zone will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
