Date and Time
 

I spool a report from a software into excel which gives me a huge data which
contains Date as below

eg. in Col A shows date with time

A1 = 10/08/2007 6:41:40 AM
A2 = 13/08/2007 12:49:00 PM
etc

I want to split this to show Date in col B and Time in Col C
i.e
A1 = 10/08/2007 6:41:40 AM
B1 = 10/08/2007
C1 = 6:41:40 AM

Note :My main aim is to later filter this file on time(Col C) and print only
those transctions which has occured after 7:00:00 PM

I tried copying the date from Col A To Col C and set format to time this
helps only for Display purpose but in reality the cell contains the entire
date (10/08/2007 6:41:40 AM) in it as such filter on time greater than
7:00:00 PM does not work!!

Thanks

Date and Time
 

In B1 =INT(A1)
In C1 =MOD(A1,1)
--
David Biddulph

"claude jerry" wrote in message
...
I spool a report from a software into excel which gives me a huge data
which
contains Date as below

eg. in Col A shows date with time

A1 = 10/08/2007 6:41:40 AM
A2 = 13/08/2007 12:49:00 PM
etc

I want to split this to show Date in col B and Time in Col C
i.e
A1 = 10/08/2007 6:41:40 AM
B1 = 10/08/2007
C1 = 6:41:40 AM

Note :My main aim is to later filter this file on time(Col C) and print
only
those transctions which has occured after 7:00:00 PM

I tried copying the date from Col A To Col C and set format to time this
helps only for Display purpose but in reality the cell contains the entire
date (10/08/2007 6:41:40 AM) in it as such filter on time greater than
7:00:00 PM does not work!!

Thanks

Date and Time
 

Thanks David it works perfect
but I have a query

suppose A1 = 01/08/2007 12:32:33 AM

but excel understands this as A1=39295.0226041667 (i.e i used general format
for A)
how can I find from where the date starts and where the time starts in this
number
or Have I got a total wrong understanding of this date concept.


"David Biddulph" wrote:

In B1 =INT(A1)
In C1 =MOD(A1,1)
--
David Biddulph

"claude jerry" wrote in message
...
I spool a report from a software into excel which gives me a huge data
which
contains Date as below

eg. in Col A shows date with time

A1 = 10/08/2007 6:41:40 AM
A2 = 13/08/2007 12:49:00 PM
etc

I want to split this to show Date in col B and Time in Col C
i.e
A1 = 10/08/2007 6:41:40 AM
B1 = 10/08/2007
C1 = 6:41:40 AM

Note :My main aim is to later filter this file on time(Col C) and print
only
those transctions which has occured after 7:00:00 PM

I tried copying the date from Col A To Col C and set format to time this
helps only for Display purpose but in reality the cell contains the entire
date (10/08/2007 6:41:40 AM) in it as such filter on time greater than
7:00:00 PM does not work!!

Thanks

Date and Time
 

Excel counts in days from the beginning of 1900 (which a slight quirk about
whether or not 1900 was leap year). Because the count is in days, the
integer part is the date and the fractional part is the time. Hence the
logic behind the formulae which I gave you.
--
David Biddulph

"claude jerry" wrote in message
...
Thanks David it works perfect
but I have a query

suppose A1 = 01/08/2007 12:32:33 AM

but excel understands this as A1=39295.0226041667 (i.e i used general
format
for A)
how can I find from where the date starts and where the time starts in
this
number
or Have I got a total wrong understanding of this date concept.


"David Biddulph" wrote:

In B1 =INT(A1)
In C1 =MOD(A1,1)
--
David Biddulph

"claude jerry" wrote in message
...
I spool a report from a software into excel which gives me a huge data
which
contains Date as below

eg. in Col A shows date with time

A1 = 10/08/2007 6:41:40 AM
A2 = 13/08/2007 12:49:00 PM
etc

I want to split this to show Date in col B and Time in Col C
i.e
A1 = 10/08/2007 6:41:40 AM
B1 = 10/08/2007
C1 = 6:41:40 AM

Note :My main aim is to later filter this file on time(Col C) and print
only
those transctions which has occured after 7:00:00 PM

I tried copying the date from Col A To Col C and set format to time
this
helps only for Display purpose but in reality the cell contains the
entire
date (10/08/2007 6:41:40 AM) in it as such filter on time greater than
7:00:00 PM does not work!!

Thanks

Date and Time
 

Thanks

That was very informative



"David Biddulph" wrote:

Excel counts in days from the beginning of 1900 (which a slight quirk about
whether or not 1900 was leap year). Because the count is in days, the
integer part is the date and the fractional part is the time. Hence the
logic behind the formulae which I gave you.
--
David Biddulph

"claude jerry" wrote in message
...
Thanks David it works perfect
but I have a query

suppose A1 = 01/08/2007 12:32:33 AM

but excel understands this as A1=39295.0226041667 (i.e i used general
format
for A)
how can I find from where the date starts and where the time starts in
this
number
or Have I got a total wrong understanding of this date concept.


"David Biddulph" wrote:

In B1 =INT(A1)
In C1 =MOD(A1,1)
--
David Biddulph

"claude jerry" wrote in message
...
I spool a report from a software into excel which gives me a huge data
which
contains Date as below

eg. in Col A shows date with time

A1 = 10/08/2007 6:41:40 AM
A2 = 13/08/2007 12:49:00 PM
etc

I want to split this to show Date in col B and Time in Col C
i.e
A1 = 10/08/2007 6:41:40 AM
B1 = 10/08/2007
C1 = 6:41:40 AM

Note :My main aim is to later filter this file on time(Col C) and print
only
those transctions which has occured after 7:00:00 PM

I tried copying the date from Col A To Col C and set format to time
this
helps only for Display purpose but in reality the cell contains the
entire
date (10/08/2007 6:41:40 AM) in it as such filter on time greater than
7:00:00 PM does not work!!

Thanks