Negative Date/Time Value Displayed as Pound Signs (###)

This article was previously published under Q112361
This article has been archived. It is offered "as is" and will no longer be updated.
For a Microsoft Excel 2000 and later version of this article, see 182247.
For a Microsoft Excel 98 version of this article, see 192291.
Symptoms
If Microsoft Excel is set to use the 1900 date system, and you add orsubtract data that is formatted with a time format, the date may not bedisplayed as you expect. When the results of the formula produce anegative value, and this data is formatted to display a time value, thecell displays pound signs, as in the following example:
########
Cause
Although the formula in the cell performs the correct operation, thenumber format returns the wrong results.
Workaround
To display the number correctly, use either of the following methods:
  • Change the date system to the 1904 date system.

    -or-
  • Use the following to change the displayed result
    =TEXT(ABS(A1-A2),"-h:mm:ss")
    where A1 has a value smaller than A2.
The above formula will display the value as text. You may need to formatthe cell alignment to right justified. Note that this is not an acceptableworkaround if you need to use the result in another calculation.
2.00 2.01 2.10 2.10c 2.10d 2.20 3.00 4.00 4.00a 8.00 97 XL97 XL7 XL5 XL4 XL3 XL
Properties

Article ID: 112361 - Last Review: 10/26/2013 11:28:00 - Revision: 2.0

  • Microsoft Excel 97 Standard Edition
  • kbnosurvey kbarchive kbprb KB112361
Feedback