How to Get Day Name from a Date in Excel (Formula)

Let’s say you have a date from that date you want to get the day name i.e. Sat, 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. And, if you want a full-day name then you can use the below formula.

=TEXT(A2,"DDDD")

Does This Work

When referring to the date into TEXT function is to convert that data into a specific text format that you have mentioned.

Using CHOOSE function 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 days from a week i.e. 1-7. If the day is Monday it will return 1 and on Tuesday it will 2. So this way, for each day of the week there is a number.

After that, in CHOOSE function there’s you have mentioned 7 different values for 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:

1. Select all the cells and press the shortcut key Ctrl + 1.
2. In the Number tab, select Custom and enter “ddd” or “dddd” in the input bar.
3. 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 just have to change its formatting.