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.

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

Convert Date to Day

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.

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

Convert Date to Day

 

  • Use Fill Handle to AutoFill the rest of the rows.

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

=TEXT(C11,"dd")

Convert Date to Day

ddd returns the day in abbreviated form:

=TEXT(C11,"ddd")

dddd returns the full day’s name:

=TEXT(C11,"dddd")

Convert Date to Day


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.

Convert Date to Day

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

Convert Date to Day

  • Press ENTER.

  • Apply Fill Handle to AutoFill the rest of the rows.

Convert Date to Day


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

 

Convert Date to Day

  • Press ENTER.

  • Apply Fill Handle to AutoFill the rest of the rows.

Convert Date to Day


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.

Convert Date to Day

A new window will appear with all the dates for the application of Power Query.

 

  • 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

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.

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

  • Right-click on the Table Name. Here, the 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.
  • 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.

Convert Date to Day

The Day’s Names are returned.

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


Practice Section

 


Download Practice Workbook

 


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Naimul Hasan Arif
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo