I think you will be O.K. with date/time datatypes. You just need to be
careful. Text to Columns can't always "see" the displayed format. For
example, if you take a cell and:

Format Cell Number Custom and enter

General" is a nice number"

in place of

General

then the number 2 would display as

2 is a nice number

But Text to Columns can't see the trailing verbiage.
--
Gary's Student


"Mike Rogers" wrote:

Gary''s Student

I have seen this solution several times here in the newsgroup, but I have a
question. Having run into the same problem on my own I solved it with
formating instead of formulas. Formate your choice of date cell with a date
formate and your choice of time cell with a time formate, use "=[original
cell]" (without brackets and quotes of course) in the desired destination
cells. Because this is a very "simple" solution and no one else has offered
it, am I setting myself up for future problems?

Mike Rogers
"Gary''s Student" wrote:

Place a single quote (apostrophe ) before the data. When running Text to
Columns, put a single separator after the date. Excel should then leave the
AM/PM alone.
--
Gary's Student


" wrote:

I posted something similar to this a while back and the solution didn't
work. Now I am back on this issue. I import from a crystal report a
summary log containing the records of when people where working. There
is a start time and end time column that are formated as such :

9/26/2006 12:35:47 AM

To extract the date from the time so I can compare who was where and on
what date etc... I use text to columns, and this is where I have
problems. For instance take the above date time stamp, when it is
broken down by date and time I have the following:

9/26/2006 (In one column) 12:35:47 PM ( In the second column)

I believe it is because the way Windows/Excel looks at the date and
time...and is reversing the actual AM/PM section. I have another
example that the date/time is:

10/18/2006 7:51:35 PM But it breaks out as: 10/18/2006 and 7:51:35
AM

How can I get the time to remain AM if it is AM and PM if it is PM???


Thanks,


Hans