How to Convert Date to Day of Week in Excel (8 Methods)

02/04/2022 represents a date, it does not provide any information of weekdays. But it is equally necessary to know the day of the week. In this article, we will discuss how to transform a date into days of the week in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


8 Methods to Convert Date to Day of Week in Excel

We will apply 8 different methods to convert date to day of the week in Excel. We will consider the following dataset for this operation.


1. Use Format Cells Option to Convert Date to Day of Week in Excel

We can convert the date to the day of the week by changing the cell format.


1.1 Change Format from Context Menu

We will use the Context Menu option to change the cell format.

Step 1:

  • First, select all the cells.
  • Press the right button of the mouse.
  • Choose Format Cells from the menu.

Format Cells Option to Convert Date to Day of Week in Excel

Step 2:

  • Choose the Custom option from the Number tab.
  • Put “dddd” on the type box and press OK.

Format Cells Option to Convert Date to Day of Week in Excel

Now, look at the dataset.

Dates are converted to days of the week.


1.2 Change Format from Ribbon

We can avail of the Format Cell option from the Number group of the Home tab.

Format Cells Option to Convert Date to Day of Week in Excel


1.3 Change Format with a Keyboard Shortcut

We can also use a keyboard shortcut to get the Format Cell option. Just press Ctrl+1 and get the Format Cell tool.

Format Cells Option to Convert Date to Day of Week in Excel

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


2. Use the TEXT Function to Convert Date to Day of Week in Excel

The TEXT function changes the representation of a value in a given text format.

We will use this TEXT function to convert the date to a day of a week. We will put our desired day format on the formula.

Step 1:

  • Go to Cell C5.
  • Put the following formula on that cell.
=TEXT(B5,"dddd")

TEXT Function to Convert Date to Day of Week in Excel

Step 2:

  • Now, press Enter and drag the Fill Handle icon.

We get the days from the date on the new column.

Read More: How to Convert Date to Month in Excel (6 Easy Methods)


3. Convert Date to Day-Number Using WEEKDAY Function

The WEEKDAY function returns the serial number of weekdays from a date value.

Step 1:

  • Go to Cell D5 and put the following formula.

Convert Date to Day-Number of a Week Using WEEKDAY Function

=WEEKDAY(B5,1)

The second argument of the formula indicates the start of the week. See the following image for other starting options.

Convert Date to Day-Number of a Week Using WEEKDAY Function

Step 2:

  • Press the Enter button and drag the fill handle icon to the last cell containing data.

Here, we get only the serial number of weekdays, not their names.

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


4. Combine CHOOSE and WEEKDAY Functions to Convert Dates to Weekdays

The CHOOSE function returns a value from given list values based on the index number.

We will combine CHOOSE and WEEKDAY functions to convert the date to days of the week in Excel.

Step 1:

  • Enter Cell C5.
  • Copy and paste the following formula.
=CHOOSE(WEEKDAY(B5),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

Combine CHOOSE & WEEKDAY Functions to Convert Dates to day in Excel

Step 2:

  • Hit the Enter button and pull the Fill Handle icon.

We get the name of days as the CHOOSE function is combined with the WEEKDAY function.

Related Content: How to Use Formula to Change Date Format in Excel (5 Methods)


Similar Readings:


5. Combine SWITCH and WEEKDAY Functions to Transform Date to Day of Week

The SWITCH function estimates a value from a range and returns the corresponding value after matching.

We will form a new formula based on the SWITCH and WEEKDAY function and get day from date values.

Step 1:

  • Put the following formula on Cell C5.

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

Combine SWITCH and WEEKDAY Functions to Transform Date to Day in Excel

Step 2:

  • Now, drag the Fill Handle icon after pressing Enter.

Related Content: How to Convert Date to Month and Year in Excel (4 Ways)


6. Get the Day Name in a Pivot Table with the WEEKDAY DAX Function

We can use the WEEKDAY DAX function in the Pivot Table to get the day name from the dates. We take a dataset of the football matches of the USA with different opponents.

Step 1:

  • First, select all cells of the dataset.
  • Select PivotTable from the Insert tab.
  • A new window will appear. Choose the Existing Worksheet option and select a cell.
  • Press tick on the Add this data to the Data Model and then press OK.

Get Day from date in a Excel Pivot Table with the WEEKDAY DAX Function

Step 2:

  • Tick on the Opponent option from the PivotTable Fields and look at the PivotTable.

Step 3:

  • Click on the left top side of the Range.
  • Select Add Measure option now.

Get Day from date in a Excel Pivot Table with the WEEKDAY DAX Function

Step 4:

  • Now, put a name in the Measure Name option.
  • And put the following formula on the formula box.
=CONCATENATEX(Range,SWITCH(WEEKDAY(Range[Date],1),1,"Sunday",2,"Monday",3,"Tuesday",4,"Wednesday",5,"Thursday",6,"Friday",7,"Saturday"),",")
Get Day from date in a Excel Pivot Table with the WEEKDAY DAX Function

Step 5:

  • Now, press OK.

Here, we get days of match schedules with different opponents.

Read More: How to Change Date Format in Pivot Table in Excel


7. Use FORMAT DAX Function in Pivot Table to Convert Dates to Weekdays

We will use the FORMAT DAX function with the PivotTable to convert dates.

Step 1:

  • First, we form a PivotTable following the steps of the previous method.

Step 2:

  • Now, go to the Measure field as shown previously. Set name in the Measure Name box.
  • Put the following formula on the mentioned box.

=CONCATENATEX(Range 1,FORMAT( Range 1[Date],"dddd" ),",")

FORMAT DAX Function in Pivot Table to Convert Date to day of week

Step 3:

  • Now, press OK.

FORMAT DAX Function in Pivot Table to Convert Date to day of week

We get the match days after conversion.

Related Content: Fix Excel Date Not Formatting Correctly (8 Quick Solutions)


8. Convert Date to Day of Week by Applying Excel Power Query

We will use simple Excel Power Query to convert data to a day of the week.

Step 1:

  • Choose From Table/Range from the Data tab.
  • Create Table window will appear. Choose the range from the dataset.
  • Tick the My table has headers and press OK.

Convert Date to Day of Week by Applying Excel Power Query

Now, the Pivot table window will appear.

Step 2:

  • Now, press the left top corner of the Date column.
  • Choose Add Column tab.
  • Choose Day from the Date option.
  • Select Name of Day from the list.

Convert Date to Day of Week by Applying Excel Power Query

Now, look at the dataset in the power query.

A new column named Day Name is added and shows the name of the days.

Read More: Excel Formula for Current Month and Year (3 Examples)


Conclusion

In this article, we described how to convert a date to a day of a week in Excel. We added 8 methods for this article. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok Paul

Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo