Need formula to subtract 5hrs from date/time field

K

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.
 
P

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
 
K

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

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

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
 
D

Dave Peterson

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

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.
 
K

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top