Readable weekdays in Excel
Posted by jpluimers on 2019/09/30
Since I always forget this: [WayBack] Exceljet: Get day name from date
If you need to get the day name (i.e. Monday, Tuesday, etc.) from a date, there are several options depending on your needs.
Basically there are four ways go get the day of the week; the first three are readable, but when ordered, they are ordered alphabetically. The last one is numeric.
Combining the numeric with the text is easier in for instance Pivot Legend Fields (Series).
So here they go (based on the above link), assuming that B4 contains a timestamp:
=WEEKDAY(B4)gives you the numeric weekday (starting with Sunday=1 to Saturday=7) which allows sorting in a meaningful order=TEXT(B4,"dddd")gives you the full day name ofB4in your locale=TEXT(B4,"ddd")gives you the shortened day name ofB4in your locale=CHOOSE(WEEKDAY(B4),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")gives you a name of your liking from a series of 7 texts
Note that depending on your locale, these formulas might actually need a semicolon:
=WEEKDAY(B4)=TEXT(B4;"dddd")=TEXT(B4;"ddd")=CHOOSE(WEEKDAY(B4);"Sun";"Mon";"Tue";"Wed";"Thu";"Fri";"Sat")
–jeroen
Leave a Reply