Excel time calculations

Time zones can be confusing. The formula is simple and gives correct result, when you remember that UTC is 10 hours BEHIND Melbourne time, in other words the difference is -10 hours. When it's 09:00 in the morning in Melbourne, it's 23:00 the evening before in UTC.

The formula is:

=MOD(B1 + (D1 / 24),1)

… where B1 is time in source time zone, and D1 is time difference between source zone and target zone.



Yes, Melbourne time zone is UTC +10, but if you want to calculate what that is in UTC, you need to take away 10 hours.

Kari

Doc943 said:

As my column of local times may be east or west of Greenwich, I assume I need to be implicitly positive or negative in column D for each row?

Yes. You need to think if the compared time zone is ahead or behind yours.

The formula is simple, and the sample formula in my last post works. The thing is, you need to think which way you want to do the time zone conversion.

A real life example: I am in time zone UTC +2 now because of the summer time. On a certain website, I work together with someone who lives in Texas, USA. Current summer time (daylight saving as they say in US) time zone for him is UTC -5.

If I want to compare local times for us both, I need to remember that he is 7 hours behind me, and that I am 7 hours ahead of him, and change formula in Excel accordingly. So, the D1 cell in my previous post would be -7 if I compared my time to his, or 7 if I compared his time to mine.

In your case, I think you have done everything correctly but that you have just forgotten that if you want to compare Melbourne time to UTC, it's minus, even if the official time zone there is plus, UTC +10.

This, knowing when you have to use a minus sign even when time zone has a plus sign is important. Always think if source time zone is ahead or behind the time zone you are comparing with.

Reading what I wrote, it really looks complicated . I really hope you can get my point.

Kari

jimbo45 said:

What's this

another "Yankee" takeover

UTC is actually GMT (Greenwich Mean Time) (longitude 0 through Greenwich S.E London).

OK Jimbo, you are now mixing two totally different things. UTC is not GMT, and it is most definitely not a "Yankee takeover".

GMT is a time zone, UTC is time standard.

Greenwich Mean Time (GMT) is often interchanged or confused with Coordinated Universal Time (UTC). But GMT is a time zone and UTC is a time standard.

Although GMT and UTC share the same current time in practice, there is a basic difference between the two:

• GMT is a time zone officially used in some European and African countries. The time can be displayed using both the 24-hour format (0 - 24) or the 12-hour format (1 - 12 am/pm).
• UTC is not a time zone, but a time standard that is the basis for civil time and time zones worldwide. This means that no country or territory officially uses UTC as a local time.

(Quote from GMT versus UTC)

The current version of UTC is defined by International Telecommunications Union Recommendation (ITU-R TF.460-6)

Time zones are told as comparison to UTC. Here in Western Europe, our normal time is UTC+1 (time zone CET), and summertime (daylight saving) is UTC+2 (time zone CEST). New York normal time, time zone EST, is UTC-5 and their summer time is time zone EDT which is UTC-4.

Short: time zones are told as how many hours behind UTC (minus X hours), or ahead it (plus X hours) they are.

Kari

Doc943 said:

The formula worked - thank you.

You are welcome

Kari said:

OK Jimbo, you are now mixing two totally different things. UTC is not GMT, and it is most definitely not a "Yankee takeover".

GMT is a time zone, UTC is time standard.


(Quote from GMT versus UTC)

The current version of UTC is defined by International Telecommunications Union Recommendation (ITU-R TF.460-6)

Time zones are told as comparison to UTC. Here in Western Europe, our normal time is UTC+1 (time zone CET), and summertime (daylight saving) is UTC+2 (time zone CEST). New York normal time, time zone EST, is UTC-5 and their summer time is time zone EDT which is UTC-4.

Short: time zones are told as how many hours behind UTC (minus X hours), or ahead it (plus X hours) they are.

Kari

Except UTC was invented decades after people used GMT+1 etc. i.e. it was a de facto standard but not rigorously defined internationally.

The problem with GMT was the imprecision of it and BST i.e. people got confused if their time differential was relative to GMT or BST, especially in countries that did not move in same way.

UTC clarified that making it unambiguous.