Excel TIME Function

Here’s a conundrum for you.

You want to find out the time in another time zone.  Let’s say New York.

Your local time in sunny Queensland, Australia right now is 4:00 AM (the sun is just coming up).

You know your local time is GMT+10, and you know that the time in New York is GMT-5.

This is starting to sound like one of those maths questions you used to get in school. You know the ones that go something like:

“Michael is two years older than three times Jennifer’s age. If Jennifer is ‘x’ years old, how would you calculate Michael’s age?”

Don’t worry I wouldn’t do that to you. My question is much simpler…well maybe:

What formula would you use in Excel to calculate the local time in New York if it’s 5:00 AM in Queensland, Australia?

Here, I’ll help you out:

The calculation you want to do is:

=4:00AM  –  (10hrs – 5hrs)

And the answer is 11:00 PM.

While this example is easy in your head it’s not so straight forward in Excel.

Problems Calculating Time in Excel

1. Excel can’t handle negative times.  So taking the example above your formula would result in a negative time and all you’d get is ######.
2. When you enter time in Excel you need to enter it in h:mm:ss format. Simply entering ‘10’ for 10 hours will confuse Excel.

Here’s my solution:

Row 4 shows components of my calculation, and row 5 shows the behind the scenes view (from the formula bar) of what I actually typed in the cells above.

1. Cell B4 – For Excel to calculate a negative time you actually need to also give that time a date. It can be any date because you’ll format the cell to h:mm AM/PM and the date won’t be visible anyway.
2. Cells C4 and D4 – I’ve used the TIME function here. The syntax for the TIME function is:

=TIME(hour,minute,second)  Notice in my example I only have hours and so I’ve left the minutes and seconds blank.

1. Cell E4 – this contains my formula =B4-(C4-D4)

Ok, you might be thinking you could have done all that in your head. But trust me. When you have family in England, Ireland and Indonesia and clients all over the world it can get pretty tiring (read ‘confusing’) constantly converting time.

For Extra Credit

I’ve actually taken it one step further (as you may expect) and incorporated a VLOOKUP table that lists all of the Time Zones.

And I’ve inserted drop down lists to select the time zone from my table with a named range of time_zone.

And then I used a VLOOKUP formula in column E like this one in cell E13:

Download the file here and use it yourself.

Of course if you only have one time zone that you’re constantly converting to then you can add a time zone to your Outlook calendar and see your local time and other time zone side by side.

Want more? Fill out the form below and sign up for our newsletter.