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 4: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 1:00 PM.
While this example is easy in your head it’s not so straight forward in Excel.
Problems Calculating Time in Excel
- 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 ######.
- 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.
- 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.
- 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.
- 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:
=B13-VLOOKUP(C13,time_zones,2,FALSE)+VLOOKUP(D13,time_zones,2,FALSE)
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.
Janice Warn says
Hi,
this has been really useful, but I can’t work out how to handle negative time.
I have a timesheet where planned staff hours and actual hours are recorded.
Over the year the staff work their full contracted hours, but on a monthly basis they may work up to 5 hours less than their contracted hours, and make the time up over busier times of the year.
Do I have to convert it back to decimals?
Janice
Mynda Treacy says
Hi Janice,
Yes, converting time to decimals is the best way to handle this scenario. This tutorial might help you with that:
https://www.myonlinetraininghub.com/excel-time-calculation-tricks
Kind regards,
Mynda
Euan says
Hi,
I’ve been trying to use your sheet but have found a problem.
— It doesn’t work for positive time differences.
For example say London 03:30 (GMT) to Paris (GMT+1:00) gives 02:30 rather than the correct answer of 04:30. I think it is because your calculation only SUBTRACTS values.
I then saw your suggested function to check the SIGN of the time difference but excel demands you use and apostrophe in form of any sign value which I think stop the function being evaluated properly.
This is a real shame as I was so close to making my scheduling sheet really nice to use – back to individual calculations it seems.
Thanks for the blog – very interesting.
Mynda Treacy says
Hi Euan,
I’ve updated the file so you can use the VLOOKUP solution to calculate the time in Paris.
Cheers,
Mynda
Euan says
That’s GREAT! Many thanks for fixing that, I couldn’t see a way around the problem. Very surprised to get such a rapid response too, brilliant.
Mynda Treacy says
You’re welcome, Euan 🙂
Gopal Rai says
Hi,
I am using this formula
=NOW()
And it gives me date and time.
I would like to use this cell to display current time only like “5:28 PM”.
Kindly guide or confirm the correct formula..
Catalin Bombea says
Hi Gopal,
Right click the cell, choose Format Cell, go to Custom section, and paste this format in the “Type” field: h:mm AM/PM
Or you can select it from list 🙂
Catalin
ron says
Nice example and example worksheet.
I had a problem downloading the example from FF V22=1, it just opened the file directly in a new FF tab. Is there a problem with the hyperlink or my setup?
Suggestion, could you add a “back” button/hyperlink to each of the example tabs. I’ve been using the “back” key, but a button would be friendlier for the mouse generation.
The Time calc is nice, it would be a little more useful if you added 2 things (I have to my copy ) an input file “Current Time” or “Lookup Time” and a new column “Relative to Current Time”.
Mynda Treacy says
Hi Ron,
I’m sorry I don’t know what you’re referring to with “FF V22=1”.
Great idea with the input time etc., thanks for sharing.
A ‘back’ button is a good idea for the workbooks. I shall add it to the To-Do list.
Kind regards,
Mynda.
Christopher White says
=TIME(9:36:30) thinking it would display 9:36:30
It says “you’ve entered too few arguments for this function”.
My question is Why? It’s the same thing you did in the example.
Christopher White says
I’m a dumb guy sometimes NOT :
=Time(,,,) Correct
It’s late please forgive me
Mynda Treacy says
No worries, Christopher. Glad you figured it out for yourself 🙂
Vittorio says
I am using this formular
=NOW()
And it gives me date,time am/pm
I would like to use this cell to workout other time zones and just see the date and time at that time.
Carlo Estopia says
Hi Vittorio,
Try a set-up like this
A1 – NOW()
B1 – -8 or + 8 (TIME DIFF)
C1 – Your Formula:
Make sure your formula’s cell is formatted as ‘DATE hh:mm’ format as well,
and the time difference as number format.
Cheers,
CarloE
Mike Wedgwood says
Re the Excel TIME function page – there is an eror in the example shown giving the time difference between Queensland and New York.
The calculation should read 4:00am – (10hr + 5hr)
and the correct answer is 1:00pm (a 15hr time difference)
Mynda Treacy says
Cheers, Mike. All fixed!