Splitting Date and Time

[Cita]

Hello,

I have data displayed as the following in one cell:

12/31/2007 00:40
01/03/2008 13:30
02/17/2008 22:08

I have tried using the "text-to-columns" function and cannot get the data to
separate correctly.

Any other suggestions? I could really use help with this one.

Thanks a lot!

 

[FSt1]

hi
understand that date/time is a single number
7/11/2008 10:49 is really kept as 39640.45108 and formated as date/time.
this is why text to column may not be working as expected. remember, with
formating, what you see on the screen may not be what's in the cell.
instead use these formula....
=DATE(YEAR(E3),MONTH(E3),DAY(E3))
=TIME(HOUR(E3),MINUTE(E3),SECOND(E3))
where e3 is where the date/time is. adjust your fit your data.

Regards
FSt1

 

[Bob Phillips]

=INT(A1)

and

=MOD(A1,1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jim Rech]

I had no problem using Text to Columns. I used Fixed Width, putting one
split point at character 10 and another at 13. I didn't import the middle
group.

--
Jim
| Hello,
|
| I have data displayed as the following in one cell:
|
| 12/31/2007 00:40
| 01/03/2008 13:30
| 02/17/2008 22:08
|
| I have tried using the "text-to-columns" function and cannot get the data
to
| separate correctly.
|
| Any other suggestions? I could really use help with this one.
|
| Thanks a lot!

 

[Cita]

That worked...you're awesome!

Thanks

hi
understand that date/time is a single number
7/11/2008 10:49 is really kept as 39640.45108 and formated as date/time.
this is why text to column may not be working as expected. remember, with
formating, what you see on the screen may not be what's in the cell.
instead use these formula....
=DATE(YEAR(E3),MONTH(E3),DAY(E3))
=TIME(HOUR(E3),MINUTE(E3),SECOND(E3))
where e3 is where the date/time is. adjust your fit your data.

Regards
FSt1