Tom’s Tutorials For Excel: Calculating Times Between Time Zones

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),,))

TimeDifferences_BeforeFormat_AfterFormat

Select the range, right-click the selection, and select Format Cells.

SelectRange_RightClick_FormatCells

In the Format Cells dialog box, on the Number tab, select Time from the Category list, select a Time format, and click OK.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
8 comments on “Tom’s Tutorials For Excel: Calculating Times Between Time Zones
  1. Ute-S says:

    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?

    • Tom Urtis says:

      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.

      • Ute-S says:

        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.

        • Tom Urtis says:

          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!

  2. Shane O'Sullivan says:

    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

  3. Shane O'Sullivan says:

    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

  4. Shane O'Sullivan says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *

*