Here’s a problem for you.

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

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

You know your local time is GMT+ 0, 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 Maggies age. If Maggie 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 London, UK?

Lets show you how to do it:

The calculation you want to do is:

=4:00AM – (0hrs + 5hrs)

And the answer is 1:00 PM.

So easy to work out on paper but not that straight forward when using excel.

**Calculating Time in Excel Known Problems**

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

**My resoloution:**

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. Remember British summer time not allowing for that will give you the wrong result.

- 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. To be exact type =now()
- 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)

The example above only works with positive numbers the example below handels both positive and negative time zones.

**Alternative Method**

incorporate a VLOOKUP table that lists all of the Time Zones.

insert drop down lists to select the time zone from my table with a named range of time_zone. (see data validation)

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

**=TIME((HOUR(B13))+VLOOKUP(D13,zone,2,FALSE),MINUTE(B13),)**

Of remember 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.