27
3
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 obtain7
=TEXT(7, "dddd")
will obtainSaturday
=TEXT(7,"dddd, yyyy-mm-dd")
will obtainSaturday, 1900-01-07
=TEXT(1,"dddd, yyyy-mm-dd")
will obtainSunday, 1900-01-01
=TEXT("1900-01-01","dddd, yyyy-mm-dd")
will also obtainSunday, 1900-01-01
The last two are wrong: the 1st of January 1900 is actually a Monday.
Various sources seem to confirm that:
- January 1900 - Wikipedia, the free encyclopedia.
- Year 1900 Calendar – The Netherlands.
- Year 1900 Calendar – United States.
What am I missing? Why is Excel doing this wrong?
1Thanks 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 – 2012-10-02T11:10:55.957