Dates/Duration as Negative Number: 1904 Date System

[jarens]

Greetings, Mr. Excel:

I was hoping an expert at Mr. Excel could help me:

I am working on a spreadsheet on duration between service calls. There are situations were the difference between one instrument problem and another results in a negative duration between 2 dates (in D:HH:MM).

Excel displays this result with XXXXX's.

I have seen the solution suggested that the date systme in the worksheet be changed to the 1904 date system. When I do this, the negative duration does display. But this 1904 Date System change also pushes my dates (start and stop date/time) to different dates. This is not acceptable.

I have also seen a solution (from Microsoft KB http://support.microsoft.com/kb/112361) recommending changing the display to text using this formula: =TEXT(ABS(A1-A2),"-h:mm:ss")

Not only does this result in a formula error, it also prevents the result from being used in another calculation (such as sum of the duration in the column).

Why does 1904 change my dates? Is there another way to have the negative duration display but also allow for us in summar calcuation?

Thanks for any input.

 

[VoG]

This shows you how to correct the dates http://support.microsoft.com/kb/180162

 

[jarens]

Thank you! I had found this Microsoft solution not long after posting this question. This does work. However, now I am finding errors in my calculations. I will post a follow up question separately.

 

[marinda]

good post!!! +1)))