Need formula to subtract 5hrs from date/time field

[Ken]

I'm working with the following date column in my excel document but it is in
GMT. I need a formula to subtract 5hrs from the time stamp in Column "G" in
order to convert it to EST.

G
CLOSE_DATE
01/08/2009 1:40:46 AM
01/08/2009 2:32:55 AM
01/08/2009 5:40:33 AM
01/08/2009 5:47:32 AM
01/08/2009 5:49:58 AM
01/08/2009 6:30:43 AM
01/08/2009 10:43:55 AM

Any help would be greatly appreciated.

 

[Pete_UK]

You just need to remember that times are stored internally in Excel as
fractions of a 24-hour day. Thus, to reduce a time by 5 hours you need
to subtract 5/24 from the time. You can do this with the following
formula:

=G2-5/24

and then copy this down, assuming your date/times are in Excel format.
You could then fix the values, and then copy/paste those to over-write
the originals in column G.

Another way is to enter this in a blank cell somewhere:

=5/24

then select that cell and click <copy>. Move the cursor and highlight
all those cells in column G with the date/time in, then click on Edit
| Paste Special | Values (check) | Subtract (check) | OK then <Esc>.

Hope this helps.

Pete

 

[Mike H]

Ken

with your date/time in a1 use

=A1-TIME(5,0,0)

Mike

 

[Ken]

I cannot get the formula to work. It seems the formula gets confused because
there is a date value as well as a time value. I tried the following formula
and it gave me the following result:

Formula
=(+TIME(5,0,0))

If I try to use =G2-TIME(5,0,0) it gives me a circular reference.


Result
01/00/1900 5:00:00 AM

instead of what I would like to see for G2 which is
01/08/2009 8:40:46 AM
instead of
01/08/2009 1:40:46 AM

Regards,

 

[Ken]

Hi Pete,

Your last option works but I need to automate it. Is there a way to execute
the formula automatically when I input a new data? The report would be more
user friendly if I didn't have to copy and paste special for column "G"
everytime I input new data.

Just wondering.

Regards,

 

[Pete_UK]

I assumed you had these date/times already in a column and wanted to
change all of them as a one-off. I didn't realise you were typing them
in yourself. Can't you just mentally subtract 5 hours as you type each
one?

The problem with trying to automate the process I described is that it
applies to all the data in column G, whereas you would want it to
apply to each cell in G as the data was entered. So, you need a
slightly different process and an event macro which will automatically
subtract those 5 hours for you from a new data entry in column G. I
can't help with this, but perhaps someone else can ...

Pete

 

[Dave Peterson]

Use a different cell (directly to the right of the input cell???) to show the
adjusted date/time.

 

[Ken]

Thanks for your help Pete. I will create a macro to change all cells within
column G. I was just wonding if there was a formula that could remain in the
cell so it would change new cell data entries on a daily basis. A macro will
work with the formula you gave me.

Thanks again.

 

[Ken]

Works like a charm Dave! I will add an additional column at the end of the
spreadsheet which will convert Column G into EST.

This helps ... thanks!