How to Convert Date to Day in Excel (7 Quick Ways)

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.

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

Convert Date to Day

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.

Convert Date to Day

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.

Convert Date to Day

We will have the result.

Similarly, the formula used for two-digit representation:

=TEXT(C11,"dd")

Convert Date to Day

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")

Convert Date to Day

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.

Convert Date to Day

  • Use Fill Handle to AutoFill the rests.

Read More: How to Convert Date to Day of Year in Excel (4 Methods)


Similar Readings:


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.

Convert Date to Day

  • Press ENTER to have the day.

  • Apply Fill Handle to AutoFill the next.

Convert Date to Day

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.

Convert Date to Day

  • Press ENTER.

  • To AutoFill, apply the Fill Handle.

Convert Date to Day

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.

Convert Date to Day

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.

Convert Date to Day

A new column will be added with the Date column containing all the days by Day Name column.

  • Click on Close & Load.

Convert Date to Day

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.

Convert Date to Day

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

Convert Date to Day

  • Now, right-click on the Table Name. Here, my table name is Convert.

  • Click on Add Measure.

Now, right-click on the Table Name. Here, my table name is Convert.

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.

Convert Date to Day

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.


Related Articles

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo