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

Read More: How to Convert Date to Number in Excel

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

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.

• Press OK.

• Click on the side box named Festival’s name and Day’s Name.

The Day’s Names are returned.

Read More: How to Convert Date to Day of Week in Excel

## Related Articles

<< Go Back to Date Format | Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF