Unable to display date correctly######dates and times are negative

[Cindy]

Using Excel 2007. Inherited a spreadsheet with a column were the numbers are
7312008, which needs to be 07/31/2008. Current format shows "general". When
I choose format cell and change to date, I get ###### with an error that
shows times are negative. Making the column wider does no good, I just get
more ###'s.

 

[Jacob Skaria]

Apply the below formula

=TEXT(TEXT(A1,"00000000"),"00-00-0000")+0
and format that to any date format

If this post helps click Yes

 

[Cindy]

Thank you Jacob! Works perfect.

Apply the below formula

=TEXT(TEXT(A1,"00000000"),"00-00-0000")+0
and format that to any date format

If this post helps click Yes

 

[David Biddulph]

The ####### isn't indicating a negative number, but a number outside Excel's
range of legal dates. The largest date value which Excel recognises is 31
Dec 9999, which corresponds to a number of 2958465. Your 7312008 is larger
than 2958465, so it is invalid.
You'll have to convert your 7312008 to 7/31/2008.
If your Windows Regional Settings (in Control Panel) recognise a format of
mddyyyy, you might get away with
=--TEXT(A1,"00\-00\-0000") and formatting the result as date.
Safer might be =DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5,2))