What day of the week is 2019-11-14? Is it a Monday, Tuesday, Wednesday, etc…

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.

1. Select the dates which we want to convert into weekday names.
2. Go to the Home tab and click on the small launch icon in the lower right corner of the Number section. This will open up the Format Cells dialog box. We can also open up the Format Cells dialog a few other ways.
   • The keyboard shortcut Ctrl + 1.
   • Right click on the selected cells ➜ choose Format Cells from the menu.
3. Go to the Number tab in the Format Cells dialog box.
4. Select Custom as the Category.
5. Add dddd into the Type field for the full weekday name or ddd for the abbreviated weekday name.
6. Press the OK button.

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.

= TEXT ( 1234 , "$#,###" )

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

= TEXT ( Value , Format )

• Value (required) is the value to convert to a text string.
• Format (required) is the formatting to apply when converting to a text string.

=TEXT ( B2, "dddd" )

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

= WEEKDAY ( Date , [Type] )

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

= WEEKDAY ( B2, 1 )

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.

= SWITCH ( WEEKDAY ( B2, 1 ),
	1, "Sun",
	2, "Mon",
	3, "Tue",
	4, "Wed",
	5, "Thu",
	6, "Fri",
	7, "Sat" )

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.

1. Select a cell inside the Excel table containing the dates.
2. Go to the Data tab in the ribbon.
3. Press the From Table/Range command in the Get & Transform Data section.

This will open up the power query editor.

We can now transform our dates into the name of the weekday.

1. We need to make sure the column is converted to the date data type. Click on the icon in the left of the column heading and select Date from the options.
2. With the date column selected, go to the Add Column tab.
3. Select Date ➜ Day ➜ Name of Day.

= Table.AddColumn( #"Changed Type", "Day Name", each Date.DayOfWeekName( [Date] ), type text )

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.

1. Select a cell inside the data.
2. Go to the Insert tab in the ribbon.
3. Press the PivotTable command.
4. In the Create PivotTable menu check the option to Add this data to the Data Model and press the OK button.

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.

1. Select a cell inside the pivot table.
2. Right click on the table in the PivotTable Fields window and select Add Measure from the menu options.

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.

= CONCATENATEX (
    Activities,
    SWITCH (
        WEEKDAY ( Activities[Date], 1 ),
        1, "Sun",
        2, "Mon",
        3, "Tue",
        4, "Wed",
        5, "Thu",
        6, "Fri",
        7, "Sat"
    ),
    ", "
)

1. Give our new measure a name like Name of Days.
2. Add the above DAX formula into the formula box.

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.

= CONCATENATEX ( Activities, FORMAT ( Activities[Date], "dddd" ), ", " )

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.

= FORMAT ( Activities[Date], "dddd" )

Inside the power pivot window, we can add our new calculated column.

1. Double click on the Add Column heading and give the new column a name like Weekday.
2. Select a cell inside the new column and add the above DAX formula into the formula bar and press enter.

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.