3 Ways to Get the Day Name for a Date - Excel Campus
21

3 Ways to Get the Day Name for a Date

Bottom line: Learn a few different ways to return the name of the day or weekday name for cell that contains a date value.

Skill level: Beginner

Get Name of Day from Date in Excel

In this post we will look at a few different ways to return the day name for a date in Excel.  This can be very useful for creating summary reports, pivot tables, and charts.

Method #1: The Number Format Drop-down Menu

Often times you have a date in a cell and you just want to know the name of the day for the date.  The easiest way to see the weekday name is to select the cell, then press the Number Format Drop-down menu button on the Home tab of the Ribbon.

The Long Date format shows a preview of the date and includes the name of the day for the date in the selected cell.

The Number Format Drop-down Displays the Weekday Name in the Long Date Format

The keyboard shortcut for the Number Format Drop-down menu is: Alt,H,N,Down Arrow

Checkout my article on how the date system works in Excel to learn more about dates.

Method #2: The Format Cells Dialog Window

We can also see the Long Date format in the Format Cells Window on the Number tab.

This window can be opened by right-clicking the cell, then pressing Format Cells…

The keyboard shortcut for the Format Cells Window is: Ctrl+1

View the Lond Date Format in the Format Cells Dialog Window

Click on the Long Date format and the Sample box will show a preview that includes the day of the week.

You can also apply a Custom number format to just display the weekday name in the cell.

Apply Custom Number Format for Weekday Name in Format Cells Window

  1. Click the Custom category in the Format Cells window.
  2. Input one of the following formats in the Type box:
    1. ddd – Returns first three letters of day name (Mon, Tue, Wed, etc.)
    2. dddd – Returns full name of the day (Monday, Tuesday, Wednesday, etc.)
  3. Press OK and the cell’s number format will be changed to display the day of the week.

The cell value will still contain the date, but the name of the day will be displayed instead of the date.

Method #3: Use the TEXT Function

The TEXT function is really handy for converting dates to different text formats.  This is great for adding fields to the source data of a pivot table.  It can be used as an alternative to the Date Grouping feature of a pivot table.

The TEXT function contains two arguments.

=TEXT( value, format_text)

  • value – The value is a number or date.  In this case we will reference the cell that contains the date.
  • format_text – This the the number format that we want to apply to the value.  The number format should be wrapped in quotes. “ddd”

The image below shows that the formula will return the day name of the given date.

Excel TEXT Function to Return Day Name based on Date Value in Cell

My friend Dave Bruns at ExcelJet has a great article that explains how to use the TEXT function for weekday names in more detail.

Use the Weekday Name in the Source Data of a Pivot Table

We can then use this technique to add a column to your pivot table source data for the day name.  This basically allows us to group dates by the day of the week.

The example below shows how I used this technique to analyze the sales trend by the day of the week.  With a pivot table you could quickly create a chart or report that shows the total or average sales by weekday for a given period.

Add Day Name column to Pivot Table Source Data with TEXT Function

We could also add a slicer with the day name to filter the pivot table or chart.  This would be similar to the technique I explained in the video on how to group days for MTD comparisons.

Add Slicer for Name of Day or Weekday for Pivot Table Chart

Checkout my free video series on pivot tables and dashboards to learn more about these great tools in Excel.

What Do You Use the Name of Day For?

Well, there are a few quick tips for getting the name of the day for a date.  Do you have any other ways to get the weekday name?

Please leave a comment below with any questions or suggestions.  Thanks!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 21 comments
Jeff Gomez - July 2, 2017

Can you insert names of days over a period of time? What formula would you use for a date range to identify range of days such as where A2 is 7/19/2017; B2 is 7/21/17 and C2 is Wed/Fri?

Reply
Tyler - May 22, 2017

Hello,

I have a sheet that I use daily that has the =TODAY() function in cell (B6) formatted as Jan 1, 2017 or whatever the current date is. I need to have another cell (E11) use that date to determine what day of the week that date is ie monday, tuesday etc and return text depending on the result.

As an example of what I need, if the date in cell B6 is equal to monday, wednesday, friday or sunday then have cell E11 display “Text A” if B6 is equal to tuesday, thursday or saturday then display “Text B”.

If it matters I have a master sheet that has the today() function in it and each day I just make a copy of the master sheet so that the date is B6 is always todays date. I am using excel 2010.

Can you help me with how to do this?

Reply
Sardar Sajid Ali - May 8, 2017

Hi jon your example find day name is very good i am solved my data

Reply
shankar - April 12, 2017

Hi,
i read many blog written by you on date. as you said excel read date as number but i got confused with the arguments of text function. could you please explain or make any video on how excel read text as compared to various format available in number tab of format cell option window, specially custom option in it.

Thanks in advance.

Reply
    Jon Acampora - April 18, 2017

    Hi Shankar,
    I’m not sure I understand your question. You can find the number format of any date in the Format Cells menu by first formatting a cell with the date format from the Date tab. Then open the format cells menu again and select the Custom tab. This will show the date format. You can use that in the 2nd argument of the TEXT function. I hope that helps. Let me know if that answers your question. I’d be happy to make a video on it. Thanks!

    Reply
Alex Auerbacher - December 6, 2016

Nevermind i saw the answer in thread.

Thank you for your help

Reply
Alex Auerbacher - December 6, 2016

Hi,

The default for retrieving the day from date is always Saturday. Is it possible to have the cells be blank if there is no date corresponding to that cell yet?

Thank you

Reply
    Jon Acampora - December 10, 2016

    Hey Alex,
    Great question! We can use an IF formula for that. Here is what the formula would look like.

    =IF(P5="","",TEXT(P5,"ddd"))

    That formula checks if cell P5 is blank. If it is blank then it returns a blank value represented by double quotes. If it’s not blank it returns the day name of the date using the TEXT function.

    Here is an article on the IF Function that should help. Thanks! 🙂

    Reply
Yashashree - November 9, 2016

Hi Jon,

I want syntax for weekday day in VBA.
I have dates in one column and i want there weekday(monday) in another column.
Can you please help me with the coding???

Reply
    Jon Acampora - November 14, 2016

    Hi Yashashree,

    In VBA you can use the Format function to return the weekday. It works similar to the Text function in Excel.

    Format(Date, “ddd”)

    I hope that helps.

    Reply
RAJESH JOGDANKAR - July 13, 2016

How numeric number convert into words?

Exp:

10200 Ten thousand two hundred rupees

Reply
Mike - April 14, 2016

Hi Jon,
I am using the text function for the weekday and the month to track students that I work with. As I work with a student I enter the information to track what I am doing, student needs, courses, programs ect. The problem I have is when I copy the formula down to the blank cells it continues to populate the cells with January and Saturday respectively. So I go from April and Wednesday for the last student I worked with to January and Saturday for a cell that is blank. I have tried to use an if function with this to no luck so far. Does this make sense?

Mike

Reply
    Jon Acampora - April 14, 2016

    Hi Mike,
    Great question! The TEXT function is evaluating those blanks as zeros and returning the day and month for a weird date (1/0/1900). Apparently January 0, 1900 was a Saturday according to Excel. 🙂 Jan 1st 1900 was a Sunday, so I guess it kind of makes sense.

    Anyways, you can use an IF formula for this. If your date is in cell A2, here is the formula.

    =IF(A2=””,””,TEXT(A2,”mmm”)

    That is basically saying that if cell A2 is blank (blank is denoted with the double quotes “”), then return a blank “”, if A2 is not blank then return the month name for the date in A2.

    Here are links to articles on the date system explained in Excel and the how to use the IF function, if you want to read more about it.

    I hope that helps. Let me know if you have any questions. Thanks!

    Reply
      Mike - April 14, 2016

      Thanks Jon,

      I entered the IF function and it works like a charm. I was close with my attempts but not quite the perfect syntax your formula consists of. Thanks again, and I look forward to learning more Excel from your blog.

      Mike

      Reply
      Riaan - December 1, 2016

      Hi Jon,
      I need to work with time sheets and overtime. Saturdays and Sundays carry a different hourly rate. I therefore need to use an IF statement to see of any date falls on a Saturday or Sunday. Problem is that I cannot look for text “Saturday” or “Sunday” in my IF criteria as the weekday name is only displayed in the cell, whereas the content of the cell is a formula.

      =IF(C3=”Saturday”,40,30)
      where Saturday hourly rate =$40 but normal weekdays = $30

      How do I get a weekday name in a format that my IF statement will work?

      Your insight will be highly appreciated.

      Reply
        Jon Acampora - December 10, 2016

        Hi Riaan,

        Great question. You can use the WEEKDAY function for this. The WEEKDAY function returns the day number of the week. It has a [return_type] argument so you can set the starting day of the week. If you set this argument to 2 then Monday will be the first day of the week and return a 1. Here is what the formula would look like if cell C3 contains a date.

        =WEEKDAY(C3,2) = 1

        With this formula you can create the following IF statement to check for a weekend.

        =IF(WEEKDAY(C3,2) >5,”Weekend”,”Weekday”)

        Again, C3 will contain the date value. I hope that helps.

        Reply
Apiyo - April 9, 2016

Jon,
I have dwonloaded data from SQL database (SAP). Ihave created a table and used that as source data for a pivot table.
Now my problem is how to change the date fotmat to Month/Date/Year like Microsoft’s default in Excel.My dates are currently displayed as Date/Month/Year.
I neededtouse TEXT function to return Day Name as you have just shown upthere.

Reply
indzara - April 6, 2016

I have usually used the TEXT function to get the weekday. Though I use Control+1 everyday, I used it only for formatting month-year combinations, and not for displaying weekday names. Thanks for sharing.

Reply

Leave a Reply: