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

• Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 2.x, 3.x, 4.x, 5.0, 5.0a
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows
• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

If Microsoft Excel is set to use the 1900 date system, and you add or subtract data that is formatted with a time format, the date may not be displayed as you expect. When the results of the formula produce a negative value, and this data is formatted to display a time value, the cell displays pound signs, as in the following example:

   ########

CAUSE

Although the formula in the cell performs the correct operation, the number format returns the wrong results.

WORKAROUND

To display the number correctly, do either of the following:

• 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.

STATUS

This behavior is by design of the versions of Microsoft Excel listed at the beginning of this article.