It’s pretty common to want to know what day of the week a given date falls on and unless you’ve got some sort of gift for knowing that, you’re going to need a way to figure it out. In Excel, there are many different ways to determine this. In this post, we’re going to explore 7 ways to achieve this task.
Format a Date as the Weekday Name
The first option we’re going to look at involves formatting our date cells. Dates in Excel are really just serial numbers starting at 1 for the date 1900-01-01. Formatting is what makes the date look like a date. There are many ways to format these serial numbers to display the date in various formats like yyyy-mm-dd, dd/mm/yyyy, dd-mmm-yy etc… One of the possible formatting options for these serial numbers is to display the weekday name with a custom dddd or ddd format.
We can format our dates from the Format Cells dialog box.
Now our dates will appear as the weekday names in the worksheet. The dates are still inside the cells and can be seen in the formula bar when a cell is selected.
Get the Weekday Name with the TEXT Function
The TEXT function will allow us to convert numbers to text and apply formatting to those numbers. We can use the TEXT function to convert the number 1234 into the text string $1,234 with the above formula. Since dates are really just serial numbers, we can use this function to convert any date into a text string with the weekday name format.
TEXT Syntax
Value (required) is the value to convert to a text string.Format (required) is the formatting to apply when converting to a text string.
The above formula will convert our date value in cell B2 into the corresponding weekday name. In this example we get a value of Friday from the date 2020-09-18.
Get the Weekday Number with the WEEKDAY Function
While the results aren’t quite as useful, there is also a WEEKDAY function in Excel. This will convert a date into a corresponding number between 1 and 7 representing the weekday.
WEEKDAY Syntax
Date (required) the date to find the weekday number from.
Type (optional) the weekday number type to return.Omitted or 1 returns 1 for Sunday through 7 for Saturday.2 returns 1 for Monday through 7 for Sunday.3 returns 0 for Monday through 6 for Sunday.11 returns 1 for Monday through 7 for Sunday.12 returns 1 for Tuesday through 7 for Monday.13 returns 1 for Wednesday through 7 for Tuesday.14 returns 1 for Thursday through 7 for Wednesday.15 returns 1 for Friday through 7 for Thursday.16 returns 1 for Saturday through 7 for Friday.17 returns 1 for Sunday through 7 for Saturday.
The above formula will convert our date value in cell B2 into the corresponding weekday number. The second argument value of 1 will return a 1 for Sunday through to 7 for a Saturday. In this case 2019-09-18 returns a 6 because it’s a Friday.
Combining SWITCH with WEEKDAY to Return the Weekday Name
On its own, the WEEKDAY function can only return a number representing the weekday, but we can combine it with the SWITCH function to get the weekday name.
The WEEKDAY function returns a number from 1 to 7 and we can then use the SWITCH function to assign a weekday name to each of these numbers.
Get the WEEKDAY Name Using Power Query
Power Query (also known as Get & Transform) is a powerful data wrangling tool available in Excel 2016 onward. It makes any data transformation easy and it can get the name of the weekday too.
We first need to import our data into the power query editor. We need our data inside an Excel table. This will open up the power query editor.
We can now transform our dates into the name of the weekday.
This will add a new column containing the weekday name and we can see the M code that’s generated in the power query formula bar. This uses the Date.DayOfWeekName power query function. A similar command can be found in the Transform tab. The difference is, this will not add a new column, but rather transform the selected column.
Get the WEEKDAY Name in a Pivot Table with the WEEKDAY DAX Function
Did you know you can summarize text values with a pivot table? Well, we can go a step further and summarize our dates as a list of weekday names inside our pivot table using a DAX measure!
We need to create a pivot table from our data. This will create a new blank pivot table in the workbook and add the data into the data model. Adding the data to the data model will allow us to use the DAX formula language with our pivot table.
Now we can create a measure to convert our dates into names and summarize the results into a comma separated list. This will open up the DAX formula editor and we can create our DAX measure.
We can now add the following formula into the DAX formula editor.
To see the results of our DAX formula, all we need to do is add it into the Values area of our PivotTable Fields window. This is very similar to the WEEKDAY function solution with Excel functions. The only difference is we need to aggregate the results with a CONCATENATEX function to display inside our pivot table.
Get the Weekday Name in a Pivot Table with the FORMAT DAX Function
Another DAX function we can use to get the weekday name is the FORMAT function. This is very similar to Excel’s TEXT function and will allow us to apply a custom format to our date values.
The process is the exact same as the previous DAX example, but instead we create a measure with the above formula.
Get the Weekday Name with a Power Pivot Calculated Column
If you have the power pivot add-in for Excel, then you can use DAX to create a calculated column in the data model. If our data isn’t already in the data model, we can easily add it by going to the Power Pivot tab in the ribbon ➜ selecting Add to Data Model.
Now we can open up the power pivot window by going to the Power Pivot tab ➜ selecting Manage Data Model. Inside the power pivot window, we can add our new calculated column.
When we close the power pivot window, we now have the new Weekday field available to use in our pivot table and we can add it into the Rows, Columns or Filter area of the pivot table.
Conclusions
There are lots of options to get the name of the day from a date in Excel. We covered formatting, Excel formulas, power query and DAX formulas in the data model. There are probably a few more ways as well. Let me know in the comments if I missed your favourite method.
