Splitting Date and Time

C

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

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
 
B

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

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

Cita

That worked...you're awesome!

Thanks :)

FSt1 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top