Excel can display the day of the week for a date without changing the underlying date value. The best method depends on whether you only want the date to look like a weekday or need the weekday name as text for another formula.
Method 1: Display a Date as a Day of the Week
- Select the cells containing your dates.
- Press Ctrl + 1 to open Format Cells.
- Select Custom.
- Enter
ddddin the Type box for the full weekday name, such as Monday. Usedddfor an abbreviated name such as Mon. - Click OK.
This changes only the display. The cell still contains the original Excel date, so sorting and date calculations continue to work normally.
Method 2: Return the Weekday Name with a Formula
If the date is in A2 and you want a separate cell containing the weekday name, use:
=TEXT(A2,"dddd")
For a three-letter abbreviation, use:
=TEXT(A2,"ddd")
The TEXT function returns text, which is useful for labels and reports but is different from preserving a real date value.
Method 3: Return a Weekday Number
Use the WEEKDAY function when you need a number that represents the day rather than its name:
=WEEKDAY(A2,2)
With the second argument set to 2, Monday is 1 and Sunday is 7. This can be useful for sorting, conditional formulas, and identifying weekends.
Why Is Excel Showing a Number Instead of a Date?
Excel stores ordinary dates as serial numbers. If a cell loses its date formatting, you may see that serial value. Apply an appropriate Date or Custom number format rather than replacing the value.
What If the Formula Does Not Work?
Make sure Excel recognizes the source as a real date. Imported data sometimes contains dates stored as text. Changing the number format alone will not convert text into a date. You may need to clean or convert the imported values first.
Also remember that ambiguous dates such as 3/4/2026 can be interpreted differently depending on regional settings. When importing important data, verify that Excel has interpreted the month and day correctly before building weekday formulas around it.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.