Alok Paul
02/04/2022 represents a date, it does not provide any information of weekdays. But it is equally necessary to know the day of the week. In this article, we will discuss how to transform a date into days of the week in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
8 Methods to Convert Date to Day of Week in Excel
We will apply 8 different methods to convert date to day of the week in Excel. We will consider the following dataset for this operation.
1. Use 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 Change Format from Context Menu
We will use the Context Menu option to change the cell format.
Step 1:
- First, select all the cells.
- Press the right button of the mouse.
- Choose Format Cells from the menu.
Step 2:
- 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 Change Format from Ribbon
We can avail of the Format Cell option from the Number group of the Home tab.
1.3 Change Format with a Keyboard Shortcut
We can also use a keyboard shortcut to get the Format Cell option. Just press Ctrl+1 and get the Format Cell tool.
Read More: How to Convert Date to Day of Year in Excel (4 Methods)
2. Use the TEXT Function to Convert Date to Day of Week in Excel
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 a week. We will put our desired day format on the formula.
Step 1:
- Go to Cell C5.
- Put the following formula on that cell.
=TEXT(B5,"dddd")
Step 2:
- Now, press Enter and drag the Fill Handle icon.
We get the days from the date on the new column.
Read More: How to Convert Date to Month in Excel (6 Easy Methods)
3. Convert Date to Day-Number Using WEEKDAY Function
The WEEKDAY function returns the serial number of weekdays from a date value.
Step 1:
- 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.
Step 2:
- 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 Day in Excel (7 Quick Ways)
4. Combine CHOOSE and WEEKDAY Functions to Convert Dates to Weekdays
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.
Step 1:
- Enter Cell C5.
- Copy and paste the following formula.
=CHOOSE(WEEKDAY(B5),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")
Step 2:
- 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.
Related Content: How to Use Formula to Change Date Format in Excel (5 Methods)
Similar Readings:
- How to Calculate First Day of Previous Month in Excel (2 Methods)
- How to Change Default Date Format from US to UK in Excel (3 Ways)
- Last Business Day of the Month in Excel (9 Examples)
- How to Get the Last Day of Month Using VBA in Excel (3 Methods)
- Remove Time from Date in Pivot Table in Excel (A Step by Step Analysis)
5. Combine 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 day from date values.
Step 1:
- 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")
Step 2:
- Now, drag the Fill Handle icon after pressing Enter.
Related Content: How to Convert Date to Month and Year in Excel (4 Ways)
6. Get the Day Name in a Pivot Table with the 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 the football matches of the USA with different opponents.
Step 1:
- 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.
Step 2:
- Tick on the Opponent option from the PivotTable Fields and look at the PivotTable.
Step 3:
- Click on the left top side of the Range.
- Select Add Measure option now.
Step 4:
- Now, put a name in the Measure Name option.
- And put the following formula on the formula box.
=CONCATENATEX(Range,SWITCH(WEEKDAY(Range[Date],1),1,"Sunday",2,"Monday",3,"Tuesday",4,"Wednesday",5,"Thursday",6,"Friday",7,"Saturday"),",")
Step 5:
- Now, press OK.
Here, we get days of match schedules with different opponents.
Read More: How to Change Date Format in Pivot Table in Excel
7. Use FORMAT DAX Function in Pivot Table to Convert Dates to Weekdays
We will use the FORMAT DAX function with the PivotTable to convert dates.
Step 1:
- First, we form a PivotTable following the steps of the previous method.
Step 2:
- Now, go to the Measure field as shown previously. Set name in the Measure Name box.
- Put the following formula on the mentioned box.
=CONCATENATEX(Range 1,FORMAT( Range 1[Date],"dddd" ),",")
Step 3:
- Now, press OK.
We get the match days after conversion.
Related Content: Fix Excel Date Not Formatting Correctly (8 Quick Solutions)
8. Convert Date to Day of Week by Applying Excel Power Query
We will use simple Excel Power Query to convert data to a day of the week.
Step 1:
- 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.
Step 2:
- Now, press the left top corner of the Date column.
- Choose 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: Excel Formula for Current Month and Year (3 Examples)
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 Exceldemy.com and give your suggestions in the comment box.
Related Articles
- How to Convert a Date to dd/mm/yyyy hh:mm:ss Format in Excel
- Get First Day of Month from Month Name in Excel (3 Ways)
- How to Get Last Day of Previous Month in Excel (3 Methods)
- Convert 7 Digit Julian Date to Calendar Date in Excel (3 Ways)
- How to Stop Excel from Auto Formatting Dates in CSV (3 Methods)
