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.

Table of Contents

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

**Select**the dates which we want to convert into weekday names.- 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.

- Go to the
**Number**tab in the Format Cells dialog box. - Select
**Custom**as the**Category**. - Add
**dddd**into the**Type**field for the full weekday name or**ddd**for the abbreviated weekday name. - 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.

- Select a cell inside the Excel table containing the dates.
- Go to the
**Data**tab in the ribbon. - 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.

- 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. - With the date column selected, go to the
**Add Column**tab. **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.

- Select a cell inside the data.
- Go to the
**Insert**tab in the ribbon. - Press the
**PivotTable**command. - 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.

- Select a cell inside the pivot table.
**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"
),
", "
)
```

- Give our new measure a name like
**Name of Days**. - 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.

- Double click on the Add Column heading and give the new column a name like
**Weekday**. - 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.

Hi John,

here’s another method to get the weekday name, using the CHOOSE function combined with WEEKDAY:

=CHOOSE(WEEKDAY(TODAY()),”Sun”,”Mon”,”Tue”,”Wed”,”Thu”,”Fri”,”Sat”)

Of course, TODAY() can be replaced by any valid date.

Best Regards,

Meni Porat

Yes, CHOOSE is another good option.

Talking about the WEEKDAY, it is well-known that this function has two “bugs” and one “flaw”:

The “bugs”:

1) it does not return the correct day of week if the date is between 1900/1/1 and 1900/2/28.

2) it considers 1900/2/29 as a valid date (which is wrong: 1900 was not a leap year)

The flaw:

it can’t calculate weekdays before 1/1/1900.

In addition, WEEKDAY returns a number, which must be further translated into a name (which your post impressively does …)

There are several solutions to the above-mentioned problems.

You are kindly invited to visit my blog and see my solution:

https://meniporat.blogspot.com/2012/07/excel-calculating-weekday-for-given-date_2236.html

Best Regards,

Meni Porat