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
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 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
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.
- Click the Custom category in the Format Cells window.
- Input one of the following formats in the Type box:
- ddd – Returns first three letters of day name (Mon, Tue, Wed, etc.)
- dddd – Returns full name of the day (Monday, Tuesday, Wednesday, etc.)
- 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.
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.
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.
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!
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.
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.
Hi Apiyo,
Great question. You can use the Text to Columns feature in Excel to convert the dates. Here is an article that explains it in more detail.
http://chandoo.org/wp/2010/03/23/text-to-date-convertion/
Let me know if you have any questions. Thanks!
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
Awesome! Thanks for letting me know and happy to hear you got it working. Have a good one! 🙂
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.
How numeric number convert into words?
Exp:
10200 Ten thousand two hundred rupees
Hi Rajesh,
You will probably need a macro for that.
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 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.
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.
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! 🙂
Nevermind i saw the answer in thread.
Thank you for your help
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 jon your example find day name is very good i am solved my data
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?
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?
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.
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.
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?
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.
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?
I want to convert a date ex-0/0/0000 in one cell to a day of the week in another cell. Any suggestions?
Thank you!
VERY HELPFUL THANK YOU
Opened new world in cell formatting. Thanks.
please advise if only get week 1 – week 4 or week 5 per month only. will reset the week in another month, any idea? Thanks
Thanks, mate.
=TEXT(A2,”dddd”)
Just adding the formula here as a way of saying thank you.
Excellent I want to learn mathematics tricks and understand how to calculate day for any date and month n years
I want to enter the Day+Date+Time all in once cell. is it possible
Sir iam aio Data number chake
how to get date format (Format Cells -> Custom -> Sample) in Capital letters i.e “FRIDAY 1 JANUARY 2016” instead of “Friday 1 January 2016”
=UPPER(put your formula here)