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:

=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 below formula.

=TEXT(A2,"DDDD")

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:

=CHOOSE(WEEKDAY(A3,2),"M","Tu","W","Th","F","Sa","Su")

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

About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

LinkedIn - YouTube

3 thoughts

Leave a Comment

Your email address will not be published.

  1. 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