Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Get the Day of Week from a Date in Excel
How to get the name of a day from a date in Excel. This returns, for example, "Tuesday" for the date October 4, 2016. I'll also show you how to use custom names for the days.
Sections:
Return Full Day Name
=TEXT(A1,"dddd")
Result:
Return Abbreviated Day Name
=TEXT(A1,"ddd")
Result:
Return Custom Day Name
With this method, you can specify the names that you want to use for each day of the week.
=CHOOSE(WEEKDAY(A1),"Sunday Funday", "Sad Monday", "Boring Tuesday", "Hump-Day", "Thursday Drink Special", "Friday Finally", "Saturday YEAH")
Result:
Here, we use the WEEKDAY function to return a number from 1 to 7; that's all that function does and, by default, 1 is Sunday and 7 is Saturday. To change which day of the week starts with 1, read our tutorial on the WEEKDAY function.
Once we know what the number is for the day of the week, we put that inside the CHOOSE function for the first argument.
The following arguments in the CHOOSE function are the values that you want to output for the days of the week. Put them in order starting with the value that you want to be output when the WEEKDAY function returns 1.
You can output any value using this method; just make sure to put double quotation marks around it if it's text.
Notes
This is a nifty little trick to get the days of the week from dates and it's really easy to use.
Make sure to download the sample file attached to this tutorial to work with these examples in Excel.
Question? Ask it in our Excel Forum
Macro: Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defin...
Tutorial: How to get the last day of the month, including the date and day of week, for any date in ...
Tutorial: How to format a Date so that only the day of the week is visible from that date. Result: ...
Tutorial: How to find a date that is so many days before or after a specified date, excluding weeken...
Tutorial: Formulas that allow you to quickly and easily remove the first or last character from a ce...
Tutorial: How to use a formula to get the first word from a cell in Excel. This works for a single c...