Take the 2-minute tour ×

This question is based on the observations of AdamV in his answer on How do I get the day name into a cell in Excel?

When A1 has the value 2009-08-01, then:

  • =WEEKDAY(A1) will obtain 7
  • =TEXT(7, "dddd") will obtain Saturday
  • =TEXT(7,"dddd, yyyy-mm-dd") will obtain Saturday, 1900-01-07
  • =TEXT(1,"dddd, yyyy-mm-dd") will obtain Sunday, 1900-01-01
  • =TEXT("1900-01-01","dddd, yyyy-mm-dd") will also obtain Sunday, 1900-01-01

The last two are wrong: the 1st of January 1900 is actually a Monday.
Various sources seem to confirm that:

What am I missing? Why is Excel doing this wrong?

share|improve this question
Thanks to this question I have slightly reworded my previous answer to make it clear that 1/1/1900 is not a Sunday, but Excel thinks it is. The inaccuracy does not change the substance of that previous answer which is that using a weekday number as a basis to produce text formatted to look like a date is flawed and unnecessary. –  AdamV Oct 2 '12 at 11:10

2 Answers 2

up vote 36 down vote accepted

As described in Microsoft KB 214058:



When the date system in Microsoft Excel was originally created, it was designed to be fully compatible with date systems used by other spreadsheet programs.

However, in this date system, the year 1900 is incorrectly interpreted as a leap year. Because there is no February 29 ("leap day") in the year 1900, the day of the week for any date before March 1, 1900 (the day after the "leap day"), is not computed correctly.

The "other spreadsheet programs" refer to Lotus 1-2-3, which was quite popular back then, and incorrectly assumed that year 1900 was a leap year. This is explained in even more detail in KB 214326:

Excel 2000 incorrectly assumes that the year 1900 is a leap year





If this behavior were to be corrected, many problems would arise, including the following:


If the behavior remains uncorrected, only one problem occurs:

share|improve this answer
+1 for the extensive answer. Accepted! –  Jeroen Wiert Pluimers Sep 30 '12 at 20:15
Here’s a related story from Stack Exchange’s very own Joel Spolsky: joelonsoftware.com/items/2006/06/16.html –  matt Sep 30 '12 at 21:47
See also. Many programmers incorrectly assume that working with dates/times is easy :) –  BlueRaja Sep 30 '12 at 22:18
Historical nitpicK: when you say 1-2-3 was "quite popular" you mean it was once the dominant spreadsheet. –  Isaac Rabinovitch Oct 2 '12 at 17:46

Here is the reason explained by Joel himself: My First BillG Review

Basic uses December 31, 1899 as the epoch instead of January 1, 1900, but for some reason, today's date was the same in Excel as it was in Basic.


I went to find an Excel developer who was old enough to remember why. Ed Fries seemed to know the answer.

"Oh," he told me. "Check out February 28th, 1900."

"It's 59," I said.

"Now try March 1st."

"It's 61!"

"What happened to 60?" Ed asked.

"February 29th. 1900 was a leap year! It's divisible by 4!"

"Good guess, but no cigar," Ed said, and left me wondering for a while.

Oops. I did some research. Years that are divisible by 100 are not leap years, unless they're also divisible by 400.

1900 wasn't a leap year.

"It's a bug in Excel!" I exclaimed.

"Well, not really," said Ed. "We had to do it that way because we need to be able to import Lotus 123 worksheets."

"So, it's a bug in Lotus 123?"

"Yeah, but probably an intentional one. Lotus had to fit in 640K. That's not a lot of memory. If you ignore 1900, you can figure out if a given year is a leap year just by looking to see if the rightmost two bits are zero. That's really fast and easy. The Lotus guys probably figured it didn't matter to be wrong for those two months way in the past.

share|improve this answer
@JeroenWiertPluimers: Actually my answer with this link was deleted by moderator and I decided to expand my answer. –  Giorgi Oct 1 '12 at 18:18

Your Answer


