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

Get FREE Advanced Excel Exercises with Solutions!

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.


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

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


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: How to Use Excel Date Shortcut


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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo