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
This behavior will occur if the following conditions are
- Microsoft Excel is set to use the 1900 date
- The value in the cell is a negative number (less than
- The cell is formatted as a date or a time.
To display the number correctly, do either of the
- Change the date system to the 1904 date system.
- Use the TEXT worksheet function to change the displayed
Steps to Change to the 1904 Date System
Microsoft Office Excel 2003 and in earlier versions of Microsoft Excel, follow these steps:
or switch to, the workbook.
- On the Tools menu, click Options, and then click the Calculation tab.
- Click to select the 1904 Date System check box.
- Click OK.
In Microsoft Office Excel 2007, follow these steps:
- Click the Microsoft Office
Button, and then click Excel Options.
- Click the Advanced
- Under When calculating this
workbook, click to select the Use 1904 date system
check box, and then click OK.
Using the TEXT Function
You 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
where A1 has a value smaller than A2.
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.