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

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

_______________________
Naz,
London


"Arsenio Oloroso" wrote:

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

Ah, yes. Very nice. I like it.

I would still like to know how to do it via Text-to-columns, however.
"Naz" wrote in message
...
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
--

_______________________
Naz,
London


"Arsenio Oloroso" wrote:

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

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

"Arsenio Oloroso" wrote in message
...
Ah, yes. Very nice. I like it.

I would still like to know how to do it via Text-to-columns, however.
"Naz" wrote in message
...
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
--

_______________________
Naz,
London


"Arsenio Oloroso" wrote:

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

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
On the Number tab, select General, then click OK.

Arsenio Oloroso wrote:
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




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html