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.

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.

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

On Jan 12, 6:43*pm, Ken wrote:
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.- Hide quoted text -

- Show quoted text -

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.



"Pete_UK" wrote:

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

On Jan 12, 6:43 pm, Ken wrote:
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.- Hide quoted text -

- Show quoted text -