splitting date and time information in cells

[mebsmith]

I have cells with the following info:

4/14/2003 14:35

but i need to split so that i can sort by date alone.

how can this be done? i have tried just taking out the data, but that just
treats the "/" as a dividing sign and so get weird numbers.

thanks in advance

 

[Dave Peterson]

If these are real dates/times, you could use:
=int(a1)
to get the date -- format it as a date.

and if you wanted
=mod(a1,1)
to get the time -- format it as time

 

[BobS]

=Int(A1) ' provides Date

=A1-Int(A1) ' provides Time

 

[mebsmith]

I have cells with the following info:

4/14/2003 14:35

but i need to split so that i can sort by date alone.

I also have cells with dates in the form:

20030416000927

where the date is the first 8 numbers. How can i extract that out?

Thanks

 

[Gord Dibben]

Data>Text to Columns>Fixed width.

Drop a line down after the 8th digit>Next

Column Data format>date>YMD

Select right column and "do not import" then Finish.


Gord Dibben MS Excel MVP

 

[mebsmith]

thanks!

Data>Text to Columns>Fixed width.

Drop a line down after the 8th digit>Next

Column Data format>date>YMD

Select right column and "do not import" then Finish.


Gord Dibben MS Excel MVP