Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
Negative date and time value are displayed as pound signs (###) in Excel
Article ID: 182247 - View products that this article applies to.
This article was previously published under Q182247
For a Microsoft Excel 98 version of this article, see 192291
For a Microsoft Excel 97 and earlier version of this article, see 112361
In Excel 2000 and later, when the results of a formula produce a negative value, and this data is formatted to display a time value or date value, the cell displays pound signs, as in the following example:
NOTE: This also occurs when you directly type a negative value into the cell.
This behavior will occur if the following conditions are true:
To display the number correctly, do either of the following:
Steps to Change to the 1904 Date SystemIn Microsoft Office Excel 2003 and in earlier versions of Microsoft Excel, follow these steps:
Using the TEXT FunctionYou can use the TEXT function to change the display of the negative number. For example, if you are subtracting A2 from A1 and you want the results displayed as a time format, you can use a formula similar to the following:
=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.
WARNING: This is not an acceptable workaround if you need to use the result in another calculation.