You are currently offline, waiting for your internet to reconnect

Negative date and time value are displayed as pound signs (###) in Excel

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

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.
SYMPTOMS
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.
CAUSE
This behavior will occur if the following conditions are true:
  • Microsoft Excel is set to use the 1900 date system

    -and-

  • The value in the cell is a negative number (less than zero).

    -and-

  • The cell is formatted as a date or a time.
RESOLUTION
To display the number correctly, do either of the following:
  • Change the date system to the 1904 date system.

    -or-
  • Use the TEXT worksheet function to change the displayed result.

Steps to Change to the 1904 Date System

In Microsoft Office Excel 2003 and in earlier versions of Microsoft Excel, follow these steps:
  1. Open, or switch to, the workbook.
  2. On the Tools menu, click Options, and then click the Calculation tab.
  3. Click to select the 1904 Date System check box.
  4. Click OK.
In Microsoft Office Excel 2007, follow these steps:
  1. Click the Microsoft Office Button, and then click Excel Options.
  2. Click the Advanced category.
  3. 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 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.
XL9 XL2002 XL2000 XL2007
Properties

Article ID: 182247 - Last Review: 01/24/2007 17:12:20 - Revision: 4.1

  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Office Excel 2003
  • Microsoft Office Excel 2007
  • kbprb KB182247
Feedback
"'><\/script>");