To get the day name from a date, use =TEXT(A2,"dddd") for the full name (Monday) or =TEXT(A2,"ddd") for the short form (Mon). If you’d rather show the day name in the date cell itself without a second column, skip the formula: select the cells, press Ctrl+1, choose Custom, and enter dddd.
The cell still holds a real date you can sort and calculate with — only the display changes, which is the difference that matters. TEXT returns text, so a column of =TEXT() results can’t be sorted chronologically.
For a custom abbreviation or a different label set, use =CHOOSE(WEEKDAY(A2,2),"M","Tu","W","Th","F","Sa","Su"), where the 2 makes Monday day 1. And to just get the day number rather than the name, =WEEKDAY(A2,2) returns 1 for Monday through 7 for Sunday.
Let’s say you have a date, and from that date, you want to get the day name, i.e. Sat or Sunday. This can be done with a formula. In fact, there is more than one formula that can be used.
Get Day Name with TEXT Function
You can use the TEXT function for extracting a day name from a date. Let’s say you have a date in cell A2, and you want to get the day name in cell B2. The formula will be:
=TEXT(A2,"DDD")
This formula will return a short day name from the date (like Mon). And if you want the full day name (like Monday), then you can use the below formula.
=TEXT(A2,"DDDD")
How Does This Work
When you refer to a date in the TEXT function, it converts that date into the text format that you have mentioned. The format code “ddd” gives the short day name, and “dddd” gives the full day name.
Using CHOOSE Function to Get the Day Name
This is another method that you can use to extract a day name from a date. And, this method is more flexible.
Let’s say you want to use a custom abbreviation or want to get the day name in a different language. The formula will be:
=CHOOSE(WEEKDAY(A3,2),"M","Tu","W","Th","F","Sa","Su")
How Does This Work
Here WEEKDAY returns the number of the day in the week, i.e. 1-7. As the second argument is 2, the week starts on Monday, so for Monday it returns 1, for Tuesday it returns 2, and for Sunday it returns 7. This way, there is a number for each day of the week.
After that, in the CHOOSE function, you have mentioned 7 different values for the 7 days of the week, and when WEEKDAY returns a number, CHOOSE returns the value according to that number. In short, you get a day name.
Apply Custom Formatting
If you don’t want to extract a day name in a different cell, you can apply custom formatting to format a date as a day name. For this:
- Select all the cells and press the shortcut key Ctrl + 1 (Command + 1 on a Mac).
- In the Number tab, select Custom and enter “ddd” or “dddd” in the Type box.
- In the end, click OK.
This will convert the date from the selected cell into a day name.
When you apply custom formatting, the value in the cell is still a date; you have just changed its formatting.
Get the Day Name for a Blank Cell or a Text Date
There are two things to watch out for with the TEXT formula. If the date cell is blank, it returns “Saturday”, as Excel treats a blank cell as 0, and day 0 of Excel’s date system is a Saturday. To leave the result blank instead, wrap the formula in IF:
=IF(A2="","",TEXT(A2,"dddd"))
And if the date is stored as text, TEXT can’t read it as a date and returns the same text back. Convert such dates into real dates first (for example, with Text to Columns or DATEVALUE).
Get the Day Name in Another Language
You can add a language code at the start of the format in TEXT to get the day name in another language. For example, [$-40C] is the code for French, so the following formula returns “lundi” for a Monday:
=TEXT(A2,"[$-40C]dddd")
Use [$-407] for German, [$-40A] for Spanish, or [$-409] for English. The last one is useful when your Excel is in another language and you want the day names in English.
Get Today’s Day Name
To get the name of the current day, use TODAY inside TEXT. The result updates every day when the workbook recalculates:
=TEXT(TODAY(),"dddd")
Frequently Asked Questions
Why does the TEXT formula not work in my language?
The format codes in TEXT follow your Excel’s language. In some languages, the letter for day is different (for example, “TTTT” in German Excel), so “dddd” doesn’t work there. Use the code for your language, or the custom formatting method, which uses the codes your Excel shows in the Format Cells dialog box.
How do I get the day number instead of the name?
Use WEEKDAY. With 2 as the second argument, it returns 1 for Monday up to 7 for Sunday:
=WEEKDAY(A2,2)
Can I sort data by the day name?
If you sort the TEXT results, Excel sorts them alphabetically (Friday, Monday, Saturday…). To sort them in the order of the week, sort by a helper column with the WEEKDAY formula instead.
really its very useful for me. and can u teach me how to get the sum
Hi Puneet,
Hope you are doing well!!
Can you please help me ?
I need to make a userform for data enter. In which that the first textbox for add month view calendar for enter date. Which is very challenging me now to resolve. Please provide some guidance for same. (Please provide the code to get calendar)
Thanks
Regards,
Harikrushna Patnaik
Hi Puneet,
In macro sheet macro no 15 I tried but shoes syntax error.