What are the first and last dates in Excel?

Have you ever considered “What is the last date that Excel can show?” or “What is the first date that Excel can show?”

I thought of this posting after reviewing a Datapig blog post that asked Excel if the world will end when the Mayan’s predicted.  It amused me but made me think:

“What is the last date in Excel?” and also, “What is the first date in excel?

Can we see the year 10,000?  Can we go back to the Middle Ages?

Well, here is the answer Smile

Did you know that Excel has 2, yes that is right, TWO different date systems?

Yes that is right, one was created for Windows and one for the Mac.

This excerpt is from Microsoft Excel Help:

“Both Microsoft Excel for the Macintosh and Excel for Windows support the 1900 and 1904 date systems. The default date system for Microsoft Office Excel for Windows is 1900; and the default date system for Microsoft Office Excel for the Macintosh is 1904.

Originally, Excel for Windows was based on the 1900 date system, because it enabled better compatibility with other spreadsheet programs that were designed to run under MS-DOS and Microsoft Windows, and therefore it became the default date system. Originally, Microsoft Excel for the Macintosh was based on the 1904 date system, because it enabled better compatibility with early Macintosh computers that did not support dates before January 2, 1904, and therefore it became the default date system.

The following table shows the first date and the last date for each date system and the serial value associated with each date.”

image

What happens if you enter a date greater than the Microsoft Excel limit?

I have verified this setting in Excel.  If you enter “1/1/10000” you will see that Excel will only display “##########” in the cell.  Here is what it looks like:

image

See the pop-up states: “Dates and times that are negative or too large display as ######.

Not sure I will ever see the year 10,000 (I am 100% positive that I will never see this Excel date), but at least Excel plans on seeing it Smile.

Video Demonstration:

Here you can see dates in action in an Excel spreadsheet:

Please make sure you sign up for our RSS feed and enter your email so that you get the latest posting.  Also, please sign up for our YouTube channel at: http://www.youtube.com/exceldashboards

Steve=True