How to Create World Time Zone Clock in Excel (2 Easy Methods)

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.

world time zone clock in excel


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.

Merge IF and TIME Functions to Create World Time Zone Clock in Excel

  • Further, AutoFill the IF and TIME functions to the rest of the cells in column F which has been given in the screenshot.

Merge IF and TIME Functions to Create World Time Zone Clock in Excel

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.

Merge IF and TIME Functions to Create World Time Zone Clock in Excel

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.

Combine MOD and TIME Functions to Create World Time Zone Clock in Excel

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


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo