Excel worksheet function – How do I get the weekday name of a date?
Posted by jpluimers on 2014/07/04
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 [Wayback/Archive] 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 [Wayback/Archive] AdamV explains in [Wayback/Archive] it is not: the outcome is OK on certain systems, but not OK on other systems.
The actual solution is even simpler, but before we go there, lets first explain what is potentially wrong with the above code:
A1has a date value=WEEKDAY(A1)
obtains an integer value
in this case:7=TEXT(7, "dddd")
obtains the weekday name of the integer value7
in this case:SATURDAY- The latter is only because of two things:
- First the way
TEXToperates:
=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 usesJanuaryof1900. - Second:
Excel thinks the1st of January 1900is aSunday(actually, it is Monday)
so for Excel, the7th of January 1900is aSaturday.
- First the way
Excel has a [Wayback/Archive] 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 – [Wayback/Archive] 2000 is not a leap year problem) which won’t get fixed as [Wayback/Archive] 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
PS – via: worksheet function – How do I get the day name into a cell in Excel? – Super User.
The Wiert Corner - irregular stream of stuff 
Leave a comment