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

02/04/2022 represents a date, it does not provide any information on the day of the week. We will convert that value to the day of the week.

We’ll consider the following dataset which has a few dates listed.

## Method 1 – Using the Format Cells Option to Convert a Date to the Day of Week in Excel

### Case 1.1 – Changing the Format from the Context Menu

Steps:

• Select all the cells.
• Right-click on the selection.
• Choose Format Cells from the menu.

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

Here’s the result.

### Case 1.2 – Changing the Format from the Ribbon

• Click on the anchor arrow in the Number group of the Home tab to get the Format Cells dialog, then follow Case 1.1.

### Case 1.3 – Changing Format with a Keyboard Shortcut

• Select the cells.
• Press Ctrl + 1 to get the Format Cells tool.

## Method 2 – Using the 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.

Steps:

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

```=TEXT(B5,"dddd") ```

• Press Enter and drag the Fill Handle icon.

## Method 3 – Converting a Date to the Day Number Using the 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.

Read More: How to Convert Date to Number in Excel

## Method 4 – Combining CHOOSE and WEEKDAY Functions in Excel to Convert a Date to the Day of Week

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

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.

## Method 5 – Joining Excel SWITCH and WEEKDAY Functions to Transform a Date to the Day of Week

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

Steps:

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

• Drag the Fill Handle icon after pressing Enter.

## Method 6 – Getting the Day Name in a Pivot Table with the WEEKDAY DAX Function

We have a dataset of football matches in the U.S. with different opponents.

Steps:

• 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.
• Check Add this data to the Data Model and press OK.

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

• Put a name in the Measure Name option.
• 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"),",")`

• Press OK.

## Method 7 – Using the FORMAT DAX Function in a Pivot Table to Convert Dates to Weekdays

Steps:

• Form a PivotTable following the steps of the previous method.

• Go to the Measure field. Set the name in the Measure Name box.
• Put the following formula in the formula box.
`=CONCATENATEX(Range 1,FORMAT( Range 1[Date],"dddd" ),",")`

• Press OK.

## Method 8 – Converting a Date to the Day of Week by Applying Excel Power Query

Steps:

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

The Power Query window will appear.

• Click on the top-left corner of the Date column.
• Choose the Add Column tab.
• Choose Day from the Date option.
• Select Name of Day from the list.

Here’s the dataset in power query.

## Related Articles

<< Go Back to Date Format | Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF