Dates and "Text-to-Columns"

[Arsenio Oloroso]

I have a database in which I am attempting to split a column of dates into
three columns.

The column looks like this:

Closed_date
2/3/1997
1/14/1997
1/10/1997
3/19/1997


I selected the column, clicked Data/Text to Columns, and using the Wizard,
specified that the text was delimited by forward slashes. I selected {none}
for text qualifier, clicked Date as the "Column Data Format."

The data preview window appeared to show exactly what I wanted--the month,
day and date parsed into three columns. But when I clicked the Finish
button, this is what I got.

Closed_date
1/2/1900 3 1997
1/1/1900 14 1997
1/1/1900 10 1997
1/3/1900 19 1997
1/3/1900 7 1997
1/3/1900 14 1997


Can anyone tell me what went wrong and how to correct this?
Any leads are much appreciated.


Arsenio

 

[Guest]

You could create a formula to do the same thing
Assuming you had the following date 25/12/2005 (or 12/25/2005 US) (xmas)
in cell a1

a b c d
1 25/12/05 =day(a1) =month(a1) = year (a1)
2

 

[Arsenio Oloroso]

Ah, yes. Very nice. I like it.

I would still like to know how to do it via Text-to-columns, however.

 

[RagDyeR]

Your original column was formatted as a date, so when TTC left only a 1, 2,
or 3 in that date formatted column, XL read those numbers as date serial
numbers, where 1 equates to 1/1/1900.

To have TTC leave numbers in that first column, you'll have to click on it
in the "Preview Window", and then click on "Text" under "Column Data
Format".

And, of course, this makes this column *not* date recognizable to XL.

--

HTH,

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

Ah, yes. Very nice. I like it.

I would still like to know how to do it via Text-to-columns, however.

 

[Debra Dalgleish]

After you close the Text to Columns dialog box, the first column should
still be selected.
To format it as a number, choose Format > Cells