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

Get FREE Advanced Excel Exercises with Solutions!

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.


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. Using TEXT Function to Display Day of Week from Date in Excel

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


2. Displaying Day of Week from Date with Format Cells Dialog Box

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 day names converted from dates.

Applying Format Cells to Display Day of Week from Date


3. Inserting Excel WEEKDAY Function to Display Day of Week from Date

Another useful way to display the day of the week from the 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


4. Combining WEEKDAY and CHOOSE Functions to Display Day of Week

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, finds the string from the list, and converts it into ‘Thu’ which is the shorter version of Thursday.


5. Joining SWITCH with WEEKDAY Function to Show Day of Week

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, finds the string from the list, and converts it into ‘Fri’ which is the shorter version of Friday.


6. Showing Day of Week in Excel 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 the day with the date as a whole whereas other methods can extract only the day of the week from the date.

Steps

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


7. Using Excel Power Query to Display Day of Week from Date

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. Displaying Day of Week from Date with Excel Pivot Table

8.1 Combining of WEEKDAY and SWITCH Functions

Finally, our last method is based on the Pivot Table. As an Excel user, it is known 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


Download Practice Workbook

Download this practice workbook.


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.


<< Go Back to Date and Time | Split | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

2 Comments
  1. This has been super super helpful, merci buckets..,

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo