Tom’s Tutorials For Excel: Calculating Times Between Time Zones
With the TIME function you can add or subtract times between time zones.
Then you can adjust the formula cells for a recognizable time format.
The formula in cell D3 copied down to cell D9 is
=IF(C3>=0,B3-TIME(C3,,),B3+TIME(ABS(C3),,))
Select the range, right-click the selection, and select Format Cells.
In the Format Cells dialog box, on the Number tab, select Time from the Category list, select a Time format, and click OK.
Thanks for this tutorial. But I still have difficulties understanding this formula. I see that ABS is necessary, because TIME can not handle negative values. But to me in “real life” it makes a huge difference, whether a location is in a time zone +6 hours or -6 hours. In your example the values in D for Honolulu and Anchorage are wrong. How can you take this into account?
Thanks for following my Excel blog page.
How are those 2 cities wrong? I might have taken daylight savings into account in that Hawaii does not observe daylight savings.
If it’s 8:52 in Honolulu, it’s 10:52 in California, not 6:52. They are west of California, so it’s earlier there, not later.
Wow, I certainly missed that one, thank you so much for pointing that out.
I will change the screen shot to show what I believe the proper formula should be to account for negative differences:
=IF(C3>=0,B3-TIME(C3,,),B3+TIME(ABS(C3),,))
Thanks again!
Hello Tom
I am glad I found your post, as it almost helps me to achieve what I need. Unfortunately, what happens is when I have 7am and a time difference of -14, the time comes up as 9pm, rather than 5pm.
Basically what I am trying to do is build a schedule of optimum times to post on FB for different cities in the world. I have the cities listed and the time I wish the post to hit in that city, then have the time differences, many of which are negative.
=IF(D9>=0,D5-TIME(D9,,),D5+TIME(ABS(D9),,)) is the formula, with 7am in D5 and time difference of -14 in D9. Yet the result here adds 14 hours instead of subtracting 14 hours.
With positive time differences, it does the opposite
=IF(N9>=0,N5-TIME(N9,,),N5+TIME(ABS(N9),,)) with 7am in N5 and N9 being 1, the result subtracts 1 hour from N5.
I tried just swapping the – and + in the formula, but that does not seem to work.
I’m sure it is something dead simple, but I am a bit of a novice with Excel and when I saw your post, it was so close to what I needed, but just seems to work in reverse.
A big thanks for any assistance you can give.
cheers
hi Tom
Actually, the issue was the nut holding the wheel, not the formula. I just wasn’t reading the result correctly.
The one thing the formula will not do, though, is work from partial hours time difference. One of the timezones I need to work with is a +3.5 time difference, but the formula still only shows 3 hours added.
Any workaround on that one? I see in one of your examples you use the round function or ceiling function, though that is way beyond my scope, to be able to use the original formula, but include either of those functions as well, as don’t wish to create a new results cell.
If the formula in D77 was =IF(C77>=0,B77-TIME(C77,,),B77+TIME(ABS(C77),,)), how would you incorporate a CELING argument using a 12 hour clock rather than 24?
cheers mate. Sorry about the earlier post
Shane
I’m not exactly sure of your question, but for dealing with international times where there is a 30-minute difference in time clocks such as with India, see if this link helps you:
http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-rounding-times-of-day-by-the-half-hour/
hi Tom
Thanks so much for the reply.
My question was how do I incorporate ROUND or CEILING into your existing formula to have the result go to the next half hour?
I used your formula in cell D77 =IF(C77>=0,B77-TIME(C77,,),B77+TIME(ABS(C77),,))
The result is a round hour, but should be a half hour as the time difference is 3.5 hours. It is just beyond me to incorporate either of those above arguments, ROUND or CEILING into this existing formula. I have tried, but with no success, so was just asking for the correct way to do it based on a 12 hour, rather than 24 hour clock.
cheers Tom