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!