Date and Time

[Guest]

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

 

[David Biddulph]

In B1 =INT(A1)
In C1 =MOD(A1,1)

 

[Guest]

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]

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.

 

[Guest]

Thanks

That was very informative

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.