At first sight you’d think that getting the weekday name of a date in Excel is as easy as this simple example using the WEEKDAY function as an intermediate:
A1 cell: 1/8/2009
B1 cell: =TEXT(WEEKDAY(A1),"dddd")This will, for the given date, print the corresponding day.
The outcome for the 1st of August 2009 (we do dd/mm/yyyy over here) is saturday, and you might think it is the right way to do it.
Well, as AdamV explains, it is not: the outcome is OK on certain systems, but not on all systems.
The actual solution is even simpler, but before we go there, lets first explain what is potentially wrong with the above code:
- A1 has a date value
- =WEEKDAY(A1)
obtains an integer value
in this case: 7 - =TEXT(7, “dddd”)
obtains the weekday name of the integer value 7
in this case: SATURDAY - The latter is only because of two things:
- First the way TEXT operates:
=TEXT(B1,”dddd, yyyy-mm-dd”)
returns this full date:
Saturday, 1900-01-07
Which means that if you don’t fill in a month or year, it uses January of 1900. - Second:
Excel thinks the 1st of January 1900 is a Sunday (actually, it is Monday)
so for Excel, the 7th of January 1900 is a Saturday.
- First the way TEXT operates:
Excel has a bug where weekdays before the 1st of March 1900 are wrong (it is the famous 1900 problem, which somewhat is the inverse of the – also famous – 2000 is not a leap year problem) which won’t get fixed as Excel wants to stay compatible with Lotus-1-2-3 which also has the bug.
So it is by luck that Excel gets the above way right.
To make sure it is always right, just format your date as “dddd” as AdamV suggests:
=TEXT(A1,"dddd")
This is much shorter than the first example, and always works well.
–jeroen
via: worksheet function – How do I get the day name into a cell in Excel? – Super User.
The Wiert Corner - irregular stream of stuff 