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" wrote:

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 somewhe

=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

On Jan 12, 5:37 pm, Ken wrote:
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.