NOTE: This also occurs when you directly type a negative value into the cell.
- Microsoft Excel is set to use the 1900 date system
- The value in the cell is a negative number (less than zero).
- The cell is formatted as a date or a time.
- Change the date system to the 1904 date system.
- Use the TEXT worksheet function to change the displayed result.
Steps to Change to the 1904 Date SystemIn Microsoft Office Excel 2003 and in earlier versions of Microsoft Excel, follow these steps:
- Open, 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.
- Click the Microsoft Office Button, and then click Excel Options.
- Click the Advancedcategory.
- Under When calculating this workbook, click to select the Use 1904 date systemcheck box, and then click OK.
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:
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.
Article ID: 182247 - Last Review: Jun 16, 2017 - Revision: 2