Get the Day of Week from a Date in Excel

Add to Favorites
Author: | Edits: don

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

Return Abbreviated Day Name

Return Custom Day Name

Notes

Return Full Day Name

=TEXT(A1,"dddd")

c6cf023f973048850188eb9c4aa0ef7f.png

Result:

3b10418d5703d7de9c4cf62ae437d328.png

Return Abbreviated Day Name

=TEXT(A1,"ddd")

c27f9339fcabe249d6ff27eab2ff4d8e.png

Result:

f2b58a55a07df36f6fc60c2fb244b66d.png

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")

772e948658195609744673129f291a8e.png

Result:

05253f09110b62f8a25a54e9b694db41.png

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


Excel Function: CHOOSE(), TEXT(), WEEKDAY()
Downloadable Files: Excel File

Similar Content on TeachExcel
Return the ISO Week Number from a Date in Excel - UDF
Macro: Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defin...
Get the Last Day of the Month in Excel
Tutorial: How to get the last day of the month, including the date and day of week, for any date in ...
Format a Date to Show Only the Day of Week in Excel
Tutorial: How to format a Date so that only the day of the week is visible from that date. Result: ...
Get a Date that is So Many Working Days Before or After a Date in Excel - WORKDAY
Tutorial: How to find a date that is so many days before or after a specified date, excluding weeken...
Formulas to Remove First or Last Character from a Cell in Excel
Tutorial: Formulas that allow you to quickly and easily remove the first or last character from a ce...
Get the First Word from a Cell in Excel
Tutorial: How to use a formula to get the first word from a cell in Excel. This works for a single c...