Naimul Hasan Arif
In our day to day life, it is a must that we have to know the day of a certain date. We can easily convert date to day in Excel.
For more simplification, we are going to use a dataset containing Festival’s Name and Date. We will convert the Date to Day.
Download Practice Workbook
7 Approaches to Convert Date to Day in Excel
1. Adopting Format Cells Feature to Convert Date to Day
The Format Cells Feature is one of the easiest ways to convert date to day.
Steps:
- Select the cells containing date that needs to convert. Here, I have selected C5:C11.
- Now, right-click on the mouse and select Format Cells.
A dialogue box will appear.
- Press on the Number tab and choose Custom from the Category.
- Now, pick a Type on how you want to convert your day. Here, I have picked “dddd” type which represents the full name of the day.
- Click OK.
And we can have the conversion.
Read More: How to Convert Date to Day of Week in Excel (8 Methods)
2. Applying TEXT Function to Convert Date to Day
We can also convert date to day by applying the TEXT Function.
Now, according to the given format_text, we will have our output.
We can use 4 types of format_text.
- d – Representation in one or two-digit of the day (eg: 5 or 24)
- dd –Representation in two-digit of the day (eg: 05 or 24)
- ddd –Representation in abbreviated form (eg: Wed, Fri)
- dddd – Representation in the full name of the day (eg: Wednesday, Friday)
Steps:
- Select a cell where you want the output. Here, I selected D5.
- Employ the TEXT Function.
My used Formula is to have one or two-digit representation:
=TEXT(C5,"d")
- Press ENTER.
We will have the result.
- Use Fill Handle to AutoFill the rests.
Similarly, the formula used for two-digit representation:
=TEXT(C11,"dd")
And the formula used for the representation in abbreviated form:
=TEXT(C11,"ddd")
And the formula used for the representation in Full form:
=TEXT(C11,"dddd")
Related Content: How to Convert Text to Date with Excel VBA (5 Ways)
3. Using WEEKDAY Function to Convert Date to Day
The WEEKDAY function will convert date to day in numerical form according to the given return type.
Steps:
- Select the cell and input the WEEKDAY Function. Anyone can choose any return type based on their choice.
- I selected C5 for the conversion and applied the WEEKDAY Function .
=WEEKDAY(C5,2)Here, return type is 2 which represents Monday as 1 through Sunday as 7.
- Press ENTER to have the desired output.
- Use Fill Handle to AutoFill the rests.
Read More: How to Convert Date to Day of Year in Excel (4 Methods)
Similar Readings
- How to Convert a Date to dd/mm/yyyy hh:mm:ss Format in Excel
- Get Last Day of Previous Month in Excel (3 Methods)
- How to Convert 7 Digit Julian Date to Calendar Date in Excel (3 Ways)
- Stop Excel from Auto Formatting Dates in CSV (3 Methods)
- How to Change Default Date Format from US to UK in Excel (3 Ways)
4. Using CHOOSE Function to Convert Date to Day
Using the CHOOSE Function is another way to convert date to day which also includes WEEKDAY Function.
Steps:
- Select a cell and employ CHOOSE Function.
- I selected cell C5. My given formula was :
=CHOOSE(WEEKDAY(C5),"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")Here, WEEKDAY Function converts the date in a day as a numerical form. Based on the number, we can choose any kind of representation. I choose here the Full form of the day name from Monday through Sunday based on the numerical value 1 to 7.
- Press ENTER to have the day.
- Apply Fill Handle to AutoFill the next.
Read More: How to Convert Date to Month and Year in Excel (4 Ways)
5. Adopting SWITCH Function to Convert Date to Day
Adaptation of the SWITCH Function can efficiently convert date to day. In SWITCH Function, the WEEKDAY Function is also applied to convert the date into the day as numerical form. Then, we have to mention both the converted value along with its representation.
Steps:
- Apply SWITCH Function on the selected cell.
- I applied the SWITCH Function in cell D5 with the formula:
=SWITCH(WEEKDAY(C11,1),1,"Monday",2,"Tuesday",3,"Wednesday",4,"Thursday",5,"Friday",6,"Saturday",7,"Sunday")Here, I select the pattern where 1 is recognized as Monday through 7 as Sunday.
- Press ENTER.
- To AutoFill, apply the Fill Handle.
Related Content: How to Use Formula to Change Date Format in Excel (5 Methods)
6. Adopting Power Query Method to Convert Date to Day
Power Query is an advanced way to convert date to day.
Steps:
- Select the dates that we want to convert. Here, i selected cells C4:C11.
- Choose Data from the ribbon and select From Table/Range.
A dialogue box will appear named Create Table.
- Press OK to create the table.
A new window will appear with all the dates for the application of Power Query.
- Follow the following order:
Add column —> Date —> Day —> Name of the Day.
A new column will be added with the Date column containing all the days by Day Name column.
- Click on Close & Load.
Hence, the results will be shown in a new sheet.
Read More: Fix Excel Date Not Formatting Correctly (8 Quick Solutions)
7. Employing WEEKDAY DAX Function in a Pivot Table to Convert Date to Day
We can employ WEEKDAY DAX Function in a Pivot Table to convert date to day.
Steps:
- Select the cells and press CTRL + T to create a table. Here, i selected cells B4:C11 to form a Table.
- Go to the Table Design tab and select Summarize with Pivot Table.
A dialogue box will appear.
- Input Table
- Select Existing Worksheet and click on the box titled Add this data to the Data Model.
- Click OK.
- Now, right-click on the Table Name. Here, my table name is Convert.
- Click on Add Measure.
A dialogue box will appear named Measure.
- Input Table Name, Measure Name, Formula, and Category.
- Then, type the following formula in Formula box.
=CONCATENATEX ( Convert, SWITCH ( WEEKDAY ( Convert[Date], 1 ), 1, "Monday", 2, "Tuesday", 3, "Wednesday", 4, "Thursday", 5, "Friday", 6, "Saturday", 7, "Sunday" ), ", " )The CONCATENATEX DAX Function is used to evaluate the expression on each row on the table. Here, the Date column in my table named Convert is evaluated on each row based on the SWITCH Function.
- Press OK.
- Now, click on the side box named Festival’s name and Day’s Name.
We can see the output on the screen.
Read More: How to Change Date Format in Pivot Table in Excel
Practice Section
For more expertise, you can practice here.
Conclusion
Every method shown here are quite easy and effective to convert date to day in Excel. Anyone can choose any process based on their choice. For further queries, leave your thoughts in the comment section.
