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

The sample dataset contains Festival’s Name and Date. We will convert the Date to Day.

Method 1 – Adopting Format Cells Feature to Convert Date to Day

Steps:

Select the cells containing date that needs to convert (C5:C11).

Right-click and select Format Cells.

A dialogue box will appear.

Click on the Number tab and choose Custom from the Category.
Pick a Type. Here, the “dddd” type is selected which represents the full name of the day.
Click OK.

The Date is converted to the Day.

Method 2 – Applying TEXT Function to Convert Date to Day

Steps:

Select a cell (D5) where the output should appear.
Enter the TEXT Function.

This example will have one or two-digit representation:

=TEXT(C5,"d")

Press ENTER.

Use Fill Handle to AutoFill the rest of the rows.

The formula can return two-digit results exclusively by specifying “dd”.

=TEXT(C11,"dd")

ddd returns the day in abbreviated form:

=TEXT(C11,"ddd")

dddd returns the full day’s name:

=TEXT(C11,"dddd")

Method 3 – Using WEEKDAY Function to Convert Date to Day

Steps:

Select the cell and input the WEEKDAY Function.

=WEEKDAY(C5,2)

Here, return type 2 is selected which means Monday is 1 and Sunday is 7.

Press ENTER.

Use Fill Handle to AutoFill the rest of the rows.

Method 4 – Using CHOOSE Function to Convert Date to Day

Steps:

Select a cell and enter the CHOOSE Function.

=CHOOSE(WEEKDAY(C5),"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")

Here, WEEKDAY converts the date in a day as a numerical form. I chose the Full form of the day name from Monday through Sunday based on the numerical value 1 to 7.

Press ENTER.

Apply Fill Handle to AutoFill the rest of the rows.

Method 5 – Adopting SWITCH Function to Convert Date to Day

Steps:

Enter SWITCH Function on the selected cell.

=SWITCH(WEEKDAY(C11,1),1,"Monday",2,"Tuesday",3,"Wednesday",4,"Thursday",5,"Friday",6,"Saturday",7,"Sunday")

Press ENTER.

Apply Fill Handle to AutoFill the rest of the rows.

Method 6 – Adopting Power Query Method to Convert Date to Day

Steps:

Select the dates that you want to convert.
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.

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.

The results are returned in a new sheet.

Method 7 – Employing WEEKDAY DAX Function in a Pivot Table to Convert Date to Day

Steps:

Select the cells and press CTRL + T to create 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.

Right-click on the Table Name. Here, the table name is Convert.

Click on Add Measure.

A dialogue box will appear named Measure.

Input Table Name, Measure Name, Formula, and Category.
Enter 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.