Excel Reformat Date Problem

  • Thread starter Thread starter Tom
  • Start date Start date
T

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
 
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
 
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
Click to expand...

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