Convert multiple dates to day of week in Excel – 3 easy ways

When working with large datasets, it is often necessary to present dates in a more intuitive format—such as the day of the week—to make your data analysis or reporting clearer. In this article, we'll explore three methods to achieve this, each method has its own advantages, and we'll provide step-by-step instructions to help you choose the best approach for your needs.

• Change date to only display day of week in cells with Format Cells function
• Change date to only display day of week with Kutools for Excel
• Change date to only display day of week in cells with formula

Change date to only display day of week in cells with Format Cells function

The Format Cells function in Excel is a quick and easy way to change how dates are displayed without altering the underlying data. This method is ideal if you want to visually represent dates as days of the week while retaining the original date values for calculations or other purposes.

1. Highlight the cells containing the dates you want to convert.
2. Right-click on the selected cells and choose Format Cells from the context menu. (Alternatively, press Ctrl + 1 to open the Format Cells dialog box.)
3. In the Format Cells dialog box, click Number tab, and choose Custom from the Category, then enter “ddd” or “dddd” in the Type box. See screenshot:
   
   Tip:

   • ddd to display the abbreviated weekday (e.g., Mon, Tue, Wed).
   • dddd to display the full weekday name (e.g., Monday, Tuesday, Wednesday).
   
4. Then click OK. All of the dates have been changed to day of week. See screenshot:
   

Change date to only display day of week with Kutools for Excel

With Kutools for Excel, you can seamlessly convert dates into meaningful days of the week in just a few clicks. Whether you prefer the full day name like "Monday" or the abbreviated version like "Mon," Kutools gives you the flexibility to customize your data presentation exactly the way you need. And that’s not all—you can even set your own unique date formatting to meet specific requirements, making it the ultimate tool for personalized and professional data management.

1. Highlight the cells containing the dates you want to convert. Then, click Kutools > Format > Apply Date Formatting.
2. In the Apply Date Formatting dialog box, select the desired day format (e.g, “Wed” or “Wednesday”).
   
3. Click OK to convert the dates to desired day of week.

Change date to only display day of week in cells with formula

If you need to convert dates to days of the week and store the result as text (rather than just changing the display format), using a formula is the best approach. This method is useful when you want to extract the day of the week for further analysis or reporting.

1. Choose the cell where you want the day of the week to appear, and enter the following formula:

=TEXT(A2, "dddd")  (for full day of the week)

=TEXT(A2, "ddd")   (for the abbreviated day)

2. Drag the fill handle (a small square at the bottom-right corner of the selected cell) to apply the formula to other cells.

Conclusion

Converting dates into weekday names in Excel can be done in various ways, each suited to different scenarios:

• Format Cells: Best for a quick display change without altering the underlying date.
• Formula(TEXT): For more flexibility and text-based results.
• Kutools for Excel: A powerful add-in for users who prefer a more automated, handle large datasets frequently.

By selecting the method that aligns with your needs—whether you require simple formatting or more robust, custom solutions—you can optimize your Excel workflow and ensure consistent, accurate results. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master Excel.