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

Article translations Article translations
Article ID: 192291 - View products that this article applies to.
This article was previously published under Q192291
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 97 and earlier version of this article, see 112361.
Expand all | Collapse all

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, 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 format the cell alignment to right justified. Note that this is not an acceptable workaround if you need to use the result in another calculation.

STATUS

This behavior is by design of Microsoft Excel 98 Macintosh Edition.

Properties

Article ID: 192291 - Last Review: October 10, 2013 - Revision: 1.0
APPLIES TO
  • Microsoft Excel 98 for Macintosh
Keywords: 
kbnosurvey kbarchive kbprb KB192291

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com