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.

Read More: How to Convert Date to Day of Year in Excel

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.

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

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.

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

Read More: How to Convert Date to Month and Year in Excel

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.

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

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

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

Download the Practice Workbook

Related Articles

• How to Convert Date to Day in Excel
• How to Convert Date to Year in Excel
• How to Convert Date to Month in Excel
• How to Convert Date to Quarter and Year in Excel

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