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.

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

On Jan 12, 5:37*pm, Ken <K...@discussions.microsoft.com> wrote:
> I'm working with the following date column in my excel document but it isin
> 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.

Ken

with your date/time in a1 use

=A1-TIME(5,0,0)

Mike

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

"Mike H" wrote:

> Ken
>
> with your date/time in a1 use
>
> =A1-TIME(5,0,0)
>
> Mike
>
> "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 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
>
> On Jan 12, 5:37 pm, Ken <K...@discussions.microsoft.com> 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 <K...@discussions.microsoft.com> 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 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
>
> > On Jan 12, 5:37 pm, Ken <K...@discussions.microsoft.com> 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 -

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



Ken wrote:
>
> 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,
>
> "Mike H" wrote:
>
> > Ken
> >
> > with your date/time in a1 use
> >
> > =A1-TIME(5,0,0)
> >
> > Mike
> >
> > "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.
> > >

--

Dave Peterson

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 <K...@discussions.microsoft.com> 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 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
> >
> > > On Jan 12, 5:37 pm, Ken <K...@discussions.microsoft.com> 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 -
>
>

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!

"Dave Peterson" wrote:

> Use a different cell (directly to the right of the input cell???) to show the
> adjusted date/time.
>
>
>
> Ken wrote:
> >
> > 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,
> >
> > "Mike H" wrote:
> >
> > > Ken
> > >
> > > with your date/time in a1 use
> > >
> > > =A1-TIME(5,0,0)
> > >
> > > Mike
> > >
> > > "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.
> > > >
>
> --
>
> Dave Peterson
>