Question about Data->Text to Columns command

Hi!

I am running Data->Text to Columns on the date column of my excel
spreadsheet. I would like it to have the MM/DD/YYYY format. The only
format available close to what I need is MDY which basically converts
all values in the column into MM/DD/YY (only 2 digits for a year).

Is there a way to specify the format that would keep year as 4-digit
number? Is there a way to customize this command?

I was adviced by one Excel guru to do this operation so I can correctly
sort my spreadsheet by the date column.

Thanks.

Try this:

Select your date cells
<Format><Cells><Number tab>
Category: Custom
Type: mm/dd/yyyy

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"success_ny@yahoo.com" wrote:

> Hi!
>
> I am running Data->Text to Columns on the date column of my excel
> spreadsheet. I would like it to have the MM/DD/YYYY format. The only
> format available close to what I need is MDY which basically converts
> all values in the column into MM/DD/YY (only 2 digits for a year).
>
> Is there a way to specify the format that would keep year as 4-digit
> number? Is there a way to customize this command?
>
> I was adviced by one Excel guru to do this operation so I can correctly
> sort my spreadsheet by the date column.
>
> Thanks.
>
>

The Text-to-Columns wants to know the input format, (ie, MD or DM) it is not concerned with the output format.
Dates are stored as numbers, see http://www.mvps.org/dmcritchie/excel/datetime.htm for a more detailed explanation.
You can set or pre-set the displayed Date to whatever form you wish.

Hope this helps

--

Originally Posted by success_ny@yahoo.com

Hi!

I am running Data->Text to Columns on the date column of my excel
spreadsheet. I would like it to have the MM/DD/YYYY format. The only
format available close to what I need is MDY which basically converts
all values in the column into MM/DD/YY (only 2 digits for a year).

Is there a way to specify the format that would keep year as 4-digit
number? Is there a way to customize this command?

I was adviced by one Excel guru to do this operation so I can correctly
sort my spreadsheet by the date column.

Thanks.