Excel Reformat Date Problem

[Tom]

Greetings;
I am have an add problem with Excel 2003.

I have a date column that is formatted i as 20050429 (yyyymmdd)

I'd really like to reformat the field to look like 04/29/2005 (mm/dd/
yyyy)

SO I thought about going into cell format and converting it to a date
format and then change it to whatever I wanted... Doesnt work...
all I get is a ########################

I thought a find and replace would do the trick... Didnt like that
one either.

What am I missing? HEEEELP!!!

Thanks
Tom

 

[Per Jessen]

Tom,

#### most often indicate that the column isn't wide enough to show the date
in the selected format.

Try to reformat your column and then change the column width.

Hopes this helps.
....
Per

 

[Ron Rosenfeld]

Greetings;
I am have an add problem with Excel 2003.

I have a date column that is formatted i as 20050429 (yyyymmdd)

I'd really like to reformat the field to look like 04/29/2005 (mm/dd/
yyyy)

SO I thought about going into cell format and converting it to a date
format and then change it to whatever I wanted... Doesnt work...
all I get is a ########################

I thought a find and replace would do the trick... Didnt like that
one either.

What am I missing? HEEEELP!!!

Thanks
Tom

Most likely, the value you are seeing is not a date formatted to look like
20050429, but rather it is just a number.

Excel stores dates as numbers with 1 = 1 Jan 1900

When you convert 20050429 to show in a date format, this results in an implied
date that is somewhere around the year 53,000! Well outside of Excel's date
specification limits (max of 31 Dec 9999), hence the "#"'s.

One solution is to use the Data/Text-to-Columns wizard.

Select your column.
Data/Text-to-Columns
Next
Next
Date: YMD
Finish

--ron

 

[Tom]

Ron, you were right on the money!!! Its fixed and looking GREAT!!!

MUCH THANX!!

Tom

 

[Ron Rosenfeld]

Ron, you were right on the money!!! Its fixed and looking GREAT!!!

MUCH THANX!!

Tom

Glad to help. Thanks for the feedback.
--ron