Convert between UTC and EST times

[Neal Lavon]

I'm doing a schedule for an international radio station and need to
list times in both EST and UTC. UTC is the old Greenwich Mean Time and
is (until Daylight Savings Time on 04/04/04) five hours ahead of EST;
4 pm in Washington is 9 pm in London.

I came up with a formula to convert EST to UTC:
[cell number]-TIMEVALUE("5:00")
and with some formatting in the resulting cell (hhmm] I can have it
read in UTC time, like 0500.

What I can't seem to get is the reverse, UTC to EST. I can reverse the
formula but I have to list the UTC time as 15:00 with the colon, and
format the result cell in AM/PM.

But is there a way to do this so a UTC time entered as 0500 can be
read as an EST time (7:30 PM) ?

Thanks for any suggestions.

Neal Lavon
Washington, D.C.

 

[JE McGimpsey]

First, UTC will be 5 hours ahead of EST even after 4/4/4. It will be
four hours ahead of EDT.

If UTC is in A1, then EST is

B1: =A1 - TIME(5,0,0) + (A1>=TIME(19,0,0)

which adds one (the coercion of boolean TRUE) after 18:59:59 UTC to
account for the UTC time being "less" than the EST (XL stores times as
fractional days, so 03:00 = 0.125, and 21:00 = 0.875):

A more compact way:

B1: =MOD(A1-TIME(5,0,0),1)

 

[Norman Harker]

Hi Neal!

EST > UTC
=A2+5/24
Format hh:mm AM/PM


UTC > EST
=D2-5/24+(D2<5/24)
Format hh:mm AM/PM

The trouble is that the date/time serial number system used by Excel
won't handle negative times. So with the UTC > EST conversion we have
to add 1 if the UTC time is earlier than 5:00 AM

If you put your time difference in C2, the formulas become more
flexible:

EST > UTC
=A2-C2/24

UTC > EST
=D2+C2/24+(D2<5/24)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Dave Hawley]

Hi Norman

RE: The trouble is that the date/time serial number system used by Excel
won't handle negative times

It does if you switch to the 1904 date system via
Tools>options-calculations. This is normally fine for most
spreadsheets.

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[Norman Harker]

Hi Dave!

I can't say that I agree except for a self contained workbook that
only covers these aspects.

Changing date systems can have adverse affects on existing date
entries.

Consider A1 where entry has been made as follows:

Ctrl + ; Space Ctrl +Shift + :
Returns: 21/03/2004 10:01:05 PM

Now change the Date System to 1904 system

A1 now shows: 22/03/2008 10:01:05 PM

We can usually work around these negative time problems and although
I'd much prefer if we didn't have to, I'd rather use a workaround.

Maybe others might come up with other situations where we could hit
problems.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[JE McGimpsey]

The date system's a red herring...

Sure, the 1904 system can handle negative times, but there are no
negative times when reporting EST or UDT.

The final answer has to be between 0:00:00 and 23:59:59, so the date
system is irrelevant.

 

[Norman Harker]

Hi JE!

Thanks and Agreed! Obviously you get used to handling these problems
being a Mac user!

Trying to get a generic solution to conversions to and from Zulu

A1: Local
B1: UTC
C1: Difference in decimal hours
D1: UTC
E1: Local

A2: User Entry
B2:
=A2-C2/24+((A2-C2/24)<1)
C2: User Entry
D2: User Entry
E2:
=D2+C2/24+((D2+C2/24)<1)

I've tested and it appears OK but confirmation would be appreciated.
(They'll be a lot of clock questions coming up as we change clocks
next weekend).


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[JE McGimpsey]

Works for me.

I've gotten used to using this form, though:

B2: =MOD(A2-C2/24,1)
E2: =MOD(D2+C2/24,1)

 

[Norman Harker]

Hi JE!

Thanks! We're ready for these time conversion questions.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Neal Lavon]

Thanks to everyone for the discussion on this situation. I don't have
the workbook here--it's at "work" naturally--but I'll give it a try on
Monday and I understand the decimal difference column, so I'll give it
a try.

Thanks again to all and I'll let you know how I fare.

Looking forward to 04-04-04 (once every 11 years) I remain,

Neal Lavon
Washington, D.C.
USA

 

[jtnyc]

I got the UTC time convertion to work, but only can get the tim
displayed correctly.

used this format: h:mm:ss

I would also like to display the date as well. when i used the forma
for date and time it just give me some like this:
#############

Can anyone help me on this

 

[Dave Peterson]

The thread for this message has disappeared off my newsreader's cache. So this
might not be anywhere close to what you need, but if your columnwidth is too
skinny, then you can see ###'s for date/time cells.

You can also get this with negative time/date (in 12/31/1899 date system).