How to Display Day of Week from Date in Excel (8 Ways)

When you are working with to-do lists or daily reports, it is inevitable to display the day of the week from the date in Excel. For any day-specific information, you need to show them in day format. Excel gives you the platform to convert a date into a day of the week. This article will provide you with all possible methods to display the day of the week from date in Excel. I hope you go through the whole article and you will be thoroughly benefitted for sure.


Download Practice Workbook

Download this practice workbook.


8 Ways to Display Day of Week from Date in Excel

To display the day of the week from the date in Excel, we find out 8 possible methods in Excel. All the methods are undoubtedly easy to understand and fairly easy to use. To show all these methods we take a dataset having some dates from which we can convert them into days.


1. Display Day of Week from Date Using TEXT Function

Firstly, the most common method to display the day of the week from the date in Excel is by using the TEXT function. The TEXT function takes dates and returns your specific format extracted from the given date.

Steps

  • First, select cell C5 where you want to apply your TEXT function.

Display Day of Week from Date Using TEXT Function

  • In the formula box, apply the following formula:
=TEXT(B5,"dddd")

Note:

To apply the TEXT function in the formula box, you can write it in two different ways.

  • TEXT(B5,”dddd”) : This formula will show the entire day’s name in the result cell which means if you apply ‘dddd’ in the formula box, it will provide you the full day name.
  • TEXT(B5,”ddd”): This ‘ddd’ will provide a short version of your required day.
  • Press Enter to apply the formula.

  • Double-click on the Fill Handle icon down the column, it will provide the day name of the corresponding date for all rows.

Display Day of Week from Date Using TEXT Function

Read More: How to Insert Day and Date in Excel (3 Ways)


2. Applying Format Cells in Excel

Secondly, we can display the day of the week from the date in Excel by using Format Cells. The Format Cells can easily convert your date into the day of the week without using any formula.

Steps

  • First, copy all the dates and paste them into column C. Now, select all the dates of the new column.

  • Now, go to the Home tab, and from the Number group, select the dialog box launcher or you can right-click on the cell and select Format Cells from the Pop-up menu.

  • A Format Cells dialog box will appear. Select the Number option and in the Category section select Custom.

  • In the Type section, type ‘dddd’ for the whole day name or type ‘ddd’ for the short name. Finally, Click on ‘OK’.

Applying Format Cells to Display Day of Week from Date

  • Finally, we get all the day names converted from dates.

Applying Format Cells to Display Day of Week from Date

Read More: How to Insert Date in Excel Formula (8 Ways)


3. Use of WEEKDAY Function to Display Day of Week from Date

Another useful way to display the day of the week from date in Excel is by using the WEEKDAY function. The WEEKDAY function converts the date into numbers from 1 to 7. Each number denotes a day of the week.

Steps

  • First, select cell C5 where you want to apply your WEEKDAY function.

  • In the formula box, write the following formula:
=WEEKDAY(B5,1)

Use of WEEKDAY Function to Display Day of Week from Date

  • Press Enter to apply the formula. It gives a number As we put 1 in the return_type parameter so, it starts the week from Sunday. So, Value 5 denotes Thursday.

  • Double-click on the Fill Handle icon down the column to apply this for all the dates.

Use of WEEKDAY Function to Display Day of Week from Date


Similar Readings


4. Combination of  WEEKDAY and CHOOSE Functions

As the WEEKDAY function doesn’t give the name of the day of the week from the date, we need to extract the text from the return number of the WEEKDAY function. To do this, we can apply the combination of the WEEKDAY and CHOOSE functions.

Steps

  • Just like other methods, select cell C5 where you want to apply the formula.

  • Now, write the following formula in the formula box.
=CHOOSE(WEEKDAY(B5),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

Combination of WEEKDAY and CHOOSE Functions to Display Day of Week from Date

  • Press Enter to apply this formula.

  • Drag the Fill Handle icon or double-click on the icon to apply this down the column.

Combination of WEEKDAY and CHOOSE Functions to Display Day of Week from Date

Breakdown of the Formula

Firstly, the WEEKDAY function provides the number of the corresponding days. By default, it starts on Sunday and the last day of the week is Saturday.

Secondly, the CHOOSE function selects the string from your given string list and converts the number into text format. In our work, the WEEKDAY function returns 5 for the first date and the

CHOOSE function takes this number and finds the string from the list and converts it into ‘Thu’ which is the shorter version of Thursday.


5. Combining SWITCH with WEEKDAY Function

You can use the combination of the SWITCH and WEEKDAY functions to display the day of the week from the date in Excel. This method is similar to the previous method. Here, the SWITCH function takes the number from the WEEKDAY function and converts it to the text.

Steps

  • Select cell C5 to apply the formula.

  • In the formula box, write the following formula
=SWITCH(WEEKDAY(B5,1),1,"Sun",2,"Mon",3,"Tue",4,"Wed",5,"Thu",6,"Fri",7,"Sat")

  • Press Enter to apply the formula.

  • Drag the Fill Handle icon or double-click on the icon down the column.

Breakdown of the Formula

The WEEKDAY function provides the number of the corresponding days. By default, it starts on Sunday and the last day of the week is Saturday.

Secondly, the SWITCH function selects the string from the string list and converts the number into text format. In cell C9, the WEEKDAY function returns 6 for the first date and the

SWITCH  function takes this number and finds the string from the list and converts it into ‘Fri’ which is the shorter version of Friday.


6. Show Day of Week from Date Using Long Date Format

Long Date format is one of the easiest formats to display the day of the week in Excel. In this format, there is no need for any kind of formula to apply. One disadvantage of this method is the Long Date format shows day with the date as a whole whereas other methods can extract only the day of the week from the date.

Steps

  • Copy dates of column B into column C and select all the cells from column C.

  • Now, go to the Home tab. To access the drop-down menu on the number bar, click on the little arrow in the Number group.

Display Day of Week from Date Using Long Date Format

  • In the drop-down menu, select Long Date.

Display Day of Week from Date Using Long Date Format

  • This will change all the dates into the Long Date format.


Similar Readings


7. Utilizing Power Query in Excel

Power Query is a powerful tool in Microsoft excel. You can do several things with this powerful tool. To display the day of the week from the date in excel, Power Query can be a good option.

Steps

  • First, you need to insert our dataset in a table to apply Power Query To do this first select the dataset.

  • Now, go to the Data tab in the ribbon and select From Table/Range.

Utilizing Power Query to Display Day of Week from Date in Excel

  • Click on ‘OK’ after putting the dataset range.

  • This will open up the Power Query editor.

Utilizing Power Query to Display Day of Week from Date in Excel

  • If your dataset is in date data type, then go to the Add Column tab, and from the Date & Time section, select Date.

Utilizing Power Query to Display Day of Week from Date in Excel

  • In the Date option, select Name of Day from Day.

  • This will create a new column beside the dataset and provides all the required day of the week from dates.

Utilizing Power Query to Display Day of Week from Date in Excel


8. Display Day of Week from Date in a Pivot Table

8.1 Combination of WEEKDAY and SWITCH Functions

Finally, our last method is based on the Pivot Table. As an Excel user, it is known to all that the Pivot Table is one of the most useful tools in Microsoft Excel. You can display the day of the week from the date in Excel by using the combination of the WEEKDAY and SWITCH functions.

Steps

  • Firstly, select the range of cells B4:B12.

  • Now, go to the Insert tab and select Pivot table from the Tables group.

Display Day of Week from Date in a Pivot Table

  • In the PivotTable dialog box, select your data table range, choose Existing Worksheet to place the PivotTable, and finally click on ‘Add this data to the Data Model.

Display Day of Week from Date in a Pivot Table

  • The PivotTable Fields will appear on the right side of the worksheet.

  • Now, right-click on Range 2 of PivotTable Fields and select Add Measure.

  • This will open up the Measure dialog box where we can create our DAX measure. Set the Category as General and give a Measure Name. Write down the following formula in the DAX Formula box and click on ‘OK’.
=CONCATENATEX (
    'Range 2',
    SWITCH (
        WEEKDAY ( 'Range 2'[Date], 1 ),
        1, "Sun",
        2, "Mon",
        3, "Tue",
        4, "Wed",
        5, "Thu",
        6, "Fri",
        7, "Sat"
    ),
    ", "
)

Display Day of Week from Date in a Pivot Table

  • Finally, you can get the day of the week from the date in Excel.

Display Day of Week from Date in a Pivot Table


8.2 Using FORMAT Function

Just like the WEEKDAY function, we can use the FORMAT function in the PivotTable Fields. In this method, we just only change the DAX formula.

Steps

  • Open the pivot table just like the previous method. Now, right-click on Range 3 in the PivotTable Fields and select Add Measure.

  • Write down the following formula in the DAX formula box and click on ‘OK
=CONCATENATEX('Range 3',FORMAT('Range 3'[Date],"dddd"),",")

Display Day of Week from Date in a Pivot Table

  • Here, we have our desired output.

Display Day of Week from Date in a Pivot Table


Conclusion

Here, we have shown eight different methods to display the day of the week from the date in Excel. All the methods are equally effective to use. I hope you enjoy the article and gain some valuable knowledge. If you have any questions, feel free to ask in the comment section, and don’t forget to visit our Exceldemy page.


Related Articles

Durjoy Paul

Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo