Separating Date and Time in a cell

[sgoyal]

I have a column of cells in the format "11/01/02 06:21". I would like to
separate the text into 2 cells - one with the date and the other with
the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks
for your help

Sameer

 

[Peo Sjoblom]

For the date use

=INT(A1)

replace A1 with the first cell of your range

for time

=MOD(A1,1)

you probably have to reformat the first to

mm/dd/yy (or whatever the setting is)

and

hh:mm

Note that you can do this by just using format but if you want to compare to
other cells with just pure date
and time values it might not work as you expect

If you get value errors then the values are text, try to copy and empty
cell, select the values and
paste special and select add, then use the formulas or the formatting

 

[sgoyal]

Brilliant! Works as promised! My guess is that the INT function is
converting the date and time to a serial value first. Thanks

Sameer

 

[Peo Sjoblom]

INT rounds down to the nearest integer and since 1 day in excel = 1 and the
date system counts day after 01/00/1900
it will return 37561 which is 37561 days after 01/00/00 then format it as
date, now the mod leaves what's left since
the value is divided by 1 and using your example it will be
0.264583333329938 which formatted as time is 06:21 since
one hour = 1/24