Convert date time group to another time zone

[bravo6]

I have a column in an excel sheet with date time formated like 02/09/09 03:31

This is in GMT (zulu) time and I want to convert it to Eastern time. So I need a formula to subtract 5 hours from the GMT cell and end up with EDT Date Time.

02/09/09 03:31 should result in 02/08/09 22:09.

It seems like this should be a pretty common task but I can't find a reference to it in excel help.

 

[SkipVought]

Hi,

Is this a ONE TIME shot or will this be on-going?

The SHORT answer is to enter 5:00 in an empty cell.

Copy that cell

Select the TIME data you want changed.

Edit > Paste Special SUBTRACT

Vola y'all!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

=GMTCell - (5/24)

Have a look at the FAQs in theis forum (Why do Dates & Times Cause problems) for an explanation of why...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PeterMoran]

Hi,

Date time in Excel is treated as if it was numbers with a day being the unit, so times are just a fraction of a day (noon =0.5).

Enter your time difference in a cell as a fraction of a day - "=5/24".

Then for your EDT just subtract the difference from GMT.

Good Luck!
Peter Moran