ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   splitting date and time information in cells (https://www.excelbanter.com/excel-discussion-misc-queries/200240-splitting-date-time-information-cells.html)

mebsmith

splitting date and time information in cells
 
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

splitting date and time information in cells
 
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



mebsmith wrote:

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

BobS

splitting date and time information in cells
 
=Int(A1) ' provides Date

=A1-Int(A1) ' provides Time



"mebsmith" wrote in message
...
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




mebsmith

splitting date and time information in cells
 


"mebsmith" wrote:

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


Gord Dibben

splitting date and time information in cells
 
DataText to ColumnsFixed width.

Drop a line down after the 8th digitNext

Column Data formatdateYMD

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


Gord Dibben MS Excel MVP

On Tue, 26 Aug 2008 12:17:04 -0700, mebsmith
wrote:



"mebsmith" wrote:

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



mebsmith

splitting date and time information in cells
 
thanks!

"Gord Dibben" wrote:

DataText to ColumnsFixed width.

Drop a line down after the 8th digitNext

Column Data formatdateYMD

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


Gord Dibben MS Excel MVP

On Tue, 26 Aug 2008 12:17:04 -0700, mebsmith
wrote:



"mebsmith" wrote:

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





All times are GMT +1. The time now is 12:18 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com