# How to Convert Date to Day of Week in Excel (8 Methods)

Get FREE Advanced Excel Exercises with Solutions!

02/04/2022 represents a date, it does not provide any information on the day of the week. But it is equally necessary to know the day of the week for a date. In this article, we will discuss how to transform a date into a day of the week in MS Excel.

Overview: Convert Date to Day of Week in Excel (Using Formulas)

We will apply 8 different methods to convert the date to the day of the week in Excel. We will consider the following dataset for this operation.

## 1. Using Format Cells Option to Convert Date to Day of Week in Excel

We can convert the date to the day of the week by changing the cell format.

### 1.1 Changing Format from Context Menu

We will use the Context Menu option to change the cell format.

Steps:

• First, select all the cells.
• Press the right button of the mouse.
• Choose Format Cells from the menu.

• Choose the Custom option from the NumberÂ tab.
• Put dddd on the Type box and press OK.

Now, look at the dataset.

Dates are converted to days of the week.

### 1.2 Changing Format from Ribbon

We can avail of the Format Cells option from the Number group of the Home tab.

### 1.3 Changing Format with a Keyboard Shortcut

We can also use a keyboard shortcut to get the Format Cells option. Just press Ctrl+1 and get the Format Cells tool.

## 2. Using Excel TEXT Function to Convert a Date to the Day of the Week

The TEXT function changes the representation of a value in a given text format.

We will use this TEXT function to convert the date to a day of the week. We will put our desired day format on the formula.

Steps:

• Go to cell C5.
• Put the following formula on that cell.

`=TEXT(B5,"dddd")`

• Now, press Enter and drag the Fill HandleÂ icon.

We get the days from the date on the new column.

## 3. Converting Date to the Day Number Using Excel WEEKDAY Function

The WEEKDAY function returns the serial number of weekdays from a date value.

Steps:

• Go to cell D5 and put the following formula.

`=WEEKDAY(B5,1)`

The second argument of the formula indicates the start of the week. See the following image for other starting options.

• Press the Enter button and drag the fill handle icon to the last cell containing data.

Here, we get only the serial number of weekdays, not their names.

Read More: How to Convert Date to Number in Excel

## 4. Combining CHOOSE and WEEKDAY Functions in Excel to Convert Date to Day of Week

The CHOOSE function returns a value from given list values based on the index number.

We will combine CHOOSE and WEEKDAY functions to convert the date to days of the week in Excel.

Steps:

• Enter Cell C5.
• Copy and paste the following formula.
`=CHOOSE(WEEKDAY(B5),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")`

• Hit the Enter button and pull the Fill HandleÂ icon.

We get the name of days as the CHOOSE function is combined with the WEEKDAY function.

## 5. Joining Excel SWITCH and WEEKDAY Functions to Transform Date to Day of Week

The SWITCH function estimates a value from a range and returns the corresponding value after matching.

We will form a new formula based on the SWITCH and WEEKDAY function and get the day from the date values.

Steps:

• Put the following formula on cell C5.
`=SWITCH(WEEKDAY(B5,1),1, "Sunday",2,Â  "Monday",3, "Tuesday",4, "Wednesday",5, "Thursday",6, "Friday",7, "Saturday")`

• Now, drag the Fill Handle icon after pressing Enter.

## 6. Getting the Day Name in Pivot Table with WEEKDAY DAX Function

We can use the WEEKDAY DAX function in the Pivot Table to get the day name from the dates. We take a dataset of football matches in the USA with different opponents.

Steps:

• First, select all cells of the dataset.
• Select PivotTable from the InsertÂ tab.
• A new window will appear. Choose the Existing Worksheet option and select a cell.
• Press tick on the Add this data to the Data Model and then press OK.

• Tick on the Opponent option from the PivotTable Fields and look at the PivotTable.

• Click on the left top side of the Range.
• Select the Add Measure option now.

• Now, put a name in the Measure NameÂ option.
• And put the following formula in the formula box.
`=CONCATENATEX(Range,SWITCH(WEEKDAY(Range[Date],1),1,"Sunday",2,"Monday",3,"Tuesday",4,"Wednesday",5,"Thursday",6,"Friday",7,"Saturday"),",")`
• Now, press OK.

Here, we get days of match schedules with different opponents.

## 7. Using FORMAT DAX Function in Pivot Table to Convert Dates to Weekdays

We will use the FORMAT DAX function with the PivotTable to convert dates.

Steps:

• First, we form a PivotTable following the steps of the previous method.

• Now, go to the Measure field as shown previously. Set the name in the Measure Name box.
• Put the following formula on the mentioned box.
`=CONCATENATEX(Range 1,FORMAT( Range 1[Date],"dddd" ),",")`

• Now, press OK.

We get the match days after conversion.

## 8. Converting Date to Day of Week by Applying Excel Power Query

We will use a simple Excel Power Query to convert data to a day of the week.

Steps:

• Choose From Table/Range from the DataÂ tab.
• Create Table window will appear. Choose the range from the dataset.
• Tick the My table has headers and press OK.

Now, the Pivot table window will appear.

• Now, press the left top corner of the DateÂ column.
• Choose the Add ColumnÂ tab.
• Choose Day from the DateÂ option.
• Select Name of Day from the list.

Now, look at the dataset in the power query.

A new column named Day Name is added and shows the name of the days.

Read More: How to Convert Date to Text YYYYMMDD

## Conclusion

In this article, we described how to convert a date to a day of a week in Excel. We added 8 methods for this article. I hope this will satisfy your needs. Please have a look at our website and give your suggestions in the comment box.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF