Search

Negative date in Excel

 

As of Excel 2019, dates ranging from January 1, 1900 (date serial number 1) through December 31, 9999 (date serial number 2958465) are supported. If you have any date value beyond this range, Excel will display an unending hash (#) error inside the Excel Cell.

If you enter a date before January 1, 1900, Excel will consider it only as a text value. Similarly, if you enter a date beyond December 31, 9999, excel will consider it only as a text value, not as a date value.

To check how Excel handles negative date values, let us enter a negative number (as negative date serial number) in an Excel Cell, which is formatted as number value. In this case, I am entering -1 as the date serial number. Again, note that currently the Cell is formatted as number. Refer below image.

negative-date-serial-number-in-excel.jpg

Right-click the Cell and select "Format Cells" from the context menu.

negative-date-serial-number-in-excel-format-cell.jpg

Format the Cell as Date, as shown below.

negative-date-serial-number-format-as-date.jpg

You can see that the Excel fills the entire width of the Cell with hash symbols. If you try to increase the width of the Column, Excel will fill the entire width again with hash symbols. Refer the image shown below.

negative-date-serial-number-hash-error.jpg

 

Related Tutorials
Cell reference (Cell address)
Range reference
Data types in Excel
Difference between Text and Number in Excel
1900 and 1904 date systems in Excel
How to change Excel date system
How to add or subtract dates in Excel
Negative date in Excel
How does Excel stores time values
How to add or subtract time in Excel
How Excel stores date and time values together
Negative time in Excel
Difference between Excel date systems
Copying dates between 1900 and 1904 date system workbooks