Learn Why Excel does not Allow Negative Dates

Learn why Excel Negative Dates are totally nonsense.

Excel works with the following range: 01/01/1900 to 12/31/999, each day in this range has a corresponding number; they go from 1 to 2,958,465. This series is called the Serial Date Time code.

What happen if you try to format a number out of this range into a Date?

Excel shows you a message like the graphic below (Excel 2003) and populate the cell with infinite ############## characters.

Graphic 1

negative dates or times are displayed as

Or like the message below if you use Excel 2007.

Graphic 2

Dates and Times that are negative or too large are displayed as

I think the 2007 message explain the issue better, it says “negative or too large” Dates instead of just “negative” as the 2003 version.

In brief…

Out of range Dates simply don’t make sense for Excel, let’s see…

Graphic 3

Negative Dates and Serial Date Time Code

Important Notice: (see graphic 3) did you see the 00/01/1900? I think is an oddity, it must be ######## too, Microsoft staff will fix this maybe in the next versions.

A final note: sometimes you format a Number as Date and receive this message by mistake, simply assign a number format again and done.