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.

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.

• In the formula box, apply the following formula:

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

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

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

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:

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

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.

• Press Enter to apply this formula.

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

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

• Press Enter to apply the formula.

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

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.

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

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

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

• This will open up the Power Query editor.

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

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

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.

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

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

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

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’

• Here, we have our desired output.

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