How to Get Day Name from a Date in Excel

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 can be used.

Get Day Name With TEXT Function

You can use TEXT function for extracting 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:


This formula will return a short day name from the date. And, if you want a full day name then you can use below formula.


get month name from a date with text function

How does this work

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

Using CHOOSE function Get Day Name

This is another method which you can use to extract 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 day name in a different language.

The formula will be:


get month name from a date with choose function

How does this work

Here WEEKDAY returns the number of day from a week i.e. 1-7. If the day is Monday it will return 1 and for 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 week and when WEEKDAY returns a number, CHOOSE returns value according to that number.

In short, you get a day name.

Apply Custom Formatting

If you don’t want to extract day name in a different cell you can apply custom formatting to format a date as a day name.

For this:

  • Select all the cell and press shortcut key Ctrl + 1.
  • In Number tab, select custom and enter “ddd” or “dddd” in the input bar.
  • In the end, click OK.
get month name from a date with custom formatting

This will convert the date from selected cell into a day name.

Note: When you apply custom formatting, the value in the cell is still a date, you just have changed it’s formatting.

Related Formulas

  1. 5 Custom Date Formats
  2. Get End of the Month Date in Excel [Incl. EOMONTH]
  3. Get First and Last Word of a Text String in Excel
  4. Get [Extract] a Month Name from a Date

Related Functions

  1. Excel TEXT Function
  2. Excel CHOOSE Function
  3. Excel WEEKDAY Function 

Content Protection by

One Comment

  1. Abhishekkumar Jasoriya 7 Sep, 17 at 1:25 pm - Reply

    Hi Puneet,
    In macro sheet macro no 15 I tried but shoes syntax error.

Leave A Comment