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!

27 comments

Your email address will not be published. Required fields are marked *

  • In the past I was able to concatenate a cell reference into a database formula in order to extract data from worksheets named after specific dates. I have work sheets names MMM-YYYY. I have the worksheet names in increasing order in the “A” column. In columns to the left on the “A” column I use database formulas to extract data from the individually named date worksheets but it won’t work any longer. The formulas concatenate the database name the “A” cell date and the rest of the database formula. It worked in the past but won’t now. Any suggestions that might make this work for me again?

  • Not gonna lie, started to code and thought it would be awesome to make some code (functions) to automate my job worksheets. I imagined that like this: I just put the value for the month and Excel lists me working days in that month or all days names for that month for another table. Hope Excel can do that. And about the lying part, Im just looking for solution for my problems with no interest in Excel.

  • what if the date is looking at have been formatted to long date, but the formula bar doesa not show the day in it only the original information, how do you find the day if your doing a lookup?

  • I have attendance sheet and I want to change weekdays according to date with formula.

    Assume that A1 number of month (1) A2 year 2018 and D5 starting date 1 2 3 4 5 6 7 …
    T F S S M T W….
    When I change month the date & weekdays need to change automatically. Please coordinate is any formula there.

  • Thanks a lot, I was in need of a cell that shows date along with the day (17-Dec-2017, Sunday) used date format –> custom = dd-mmm-yyyy, dddd.

  • 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?

  • 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?

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

    • 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!

  • 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

    • 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! 🙂

  • 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???

  • 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

    • 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!

      • 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

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

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

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

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

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly