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.
Read More: How to Convert Date to Day of Year in Excel
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.
Read More: How to Convert Date to Month and Year in Excel
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
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
