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

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.

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

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


"Cindy" wrote:

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.

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

Thank you Jacob! Works perfect.

"Jacob Skaria" wrote:

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


"Cindy" wrote:

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.

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

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))
--
David Biddulph

"Cindy" wrote in message
...
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.