Text to Columns issue with Date & Time

[C Andrews]

I exported a report from our problem tracking system into Excel, and th
date and time show up in one cell together with a space between. Ex
10/25/04 14:56.
When I try to split them into two columns using the 'Text to Columns
feature, (space delimited) it changes it to three columns that loo
like this:
A) 10/25/04 0:00 B) 1/0/00 2:56 C) PM

I don't want the 0:00 in column A, or the 1/0/00 in column B, and m
preference is that it would leave the time in a 24 hour format
although that's not critical. Ideal would be A) 10/25/04 B
14:56.

I tried formatting the column as 'Text' before I did text to columns
but it changed the data to 38285.62267. I also tried choosing 'Custom
from the Number tab in formatting and set it to a type of 'm/d/yyy
h:mm', but I got the same result when I did text to columns.

Any ideas on how to make this work

 

[JulieD]

Hi

as excel stores date & time as serial numbers (e.g. the 38285.62267 that you
saw when you formatted it to text) you can't use data / text to columns to
split it up (AFAIK)
if you just want to display the date in one column and the time in another
you can copy to date across to these two cells and format the first only to
show the date and the second only to show the time (format / cells - play
with the settings under date) ... this will not change the value from
38285.62267 but "hide" the bit you don't want.

If, for some reason, you need to change the values then you'll need to use
the following formula (with your data in A1)
=TEXT(INT(A1),"mm/dd/yy")
=TEXT(MOD(A1,1),"hh:mm")
(note this changes them to text, you can leave the text function off, and
then use the format option to hide the bit you don't want)

Hope this helps
Cheers
JulieD

 

[Guest]

hi,
try text to columns fixed width. this way you can split
the datatime into 2 columns, date and time.
you may have to reformat both columns.
regards

 

[RagDyeR]

Try this:

In *addition* to checking <space> as the delimiter, also check "other",
And in the box enter
<Alt>0160
using the numbers from the num keypad, *not* the numbers under the function
keys.
You will *not* see anything in the box, since this is the code for a
"non-breaking" space.

Now look at the "Data Preview" window (which is WYSIWYG), and see what's
displayed there.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



I exported a report from our problem tracking system into Excel, and the
date and time show up in one cell together with a space between. Ex.
10/25/04 14:56.
When I try to split them into two columns using the 'Text to Columns'
feature, (space delimited) it changes it to three columns that look
like this:
A) 10/25/04 0:00 B) 1/0/00 2:56 C) PM

I don't want the 0:00 in column A, or the 1/0/00 in column B, and my
preference is that it would leave the time in a 24 hour format,
although that's not critical. Ideal would be A) 10/25/04 B)
14:56.

I tried formatting the column as 'Text' before I did text to columns,
but it changed the data to 38285.62267. I also tried choosing 'Custom'
from the Number tab in formatting and set it to a type of 'm/d/yyyy
h:mm', but I got the same result when I did text to columns.

Any ideas on how to make this work?