How to Display the Day of the Week with a Date in Excel
Question details
The user wants to modify cell formatting in Excel to show both the day of the week and the calendar date.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- The user needs to present dates more clearly by including the corresponding weekday (e.g., Wednesday, 14 September 2022) in their spreadsheet.
- Observed behavior
- The cells currently display only the standard calendar date without the day of the week, which requires adjusting the cell's number format.
Ensure the cells you intend to format contain valid date values recognized by the software, rather than plain text strings that just look like dates.
Use Custom Number Formatting
Apply a specific custom format code to precisely control how the day, month, and year are displayed in your cells.
Custom number formatting allows you to define exactly how a date should appear without altering the underlying data. By using the 'd' character repeatedly, you can command the software to display the full or abbreviated day of the week.
Highlight the cells containing the dates you want to format.
Right-click the selected cells and choose Format Cells, or simply press the Ctrl+1 shortcut on your keyboard.
In the Format Cells dialog box, go to the Number tab and select Custom from the list of categories on the left.
In the Type box, enter the code 'dddd, d mmmm yyyy' (for Wednesday, 14 September 2022) or 'dddd, mmmm d, yyyy' (for Wednesday, September 14, 2022), then click OK.
Choose a Built-in Date Format
Quickly change the date's appearance by selecting a pre-defined format that includes the weekday, depending on your system's regional settings.
Format Dates and Weekdays Seamlessly in WPS Spreadsheet
WPS Spreadsheet provides intuitive cell formatting identical to Excel, allowing you to easily display weekdays alongside dates. Enjoy powerful data management in a lightweight, free office suite.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing your date values.
- 2. Select and open Format Cells: Highlight the date cells, right-click, and select Format Cells, or use the Ctrl+1 keyboard shortcut.
- 3. Choose Custom formatting: Under the Number tab, select Custom from the category list.
- 4. Apply the day of week format: Type 'dddd, d mmmm yyyy' in the Type box and hit OK to instantly reveal the day of the week.
Frequently Asked Questions
How do I display only the day of the week without the date?
Open the Format Cells dialog box (Ctrl+1), navigate to the Custom category, and enter 'dddd' in the Type input field. This will display only the day name (like Monday) while preserving the underlying date value for calculations.
Why isn't the custom date format changing my cell's appearance?
This usually happens when the software treats your date as text rather than a real date value. Try double-clicking the cell and pressing Enter, or use the Text to Columns feature on the Data tab to convert the text back into recognizable dates.
Can I use a formula to extract the day of the week instead of formatting?
Yes, you can use the TEXT function to extract it into a separate cell. For example, entering =TEXT(A1, "dddd") in an adjacent cell will output the day of the week based on the date present in cell A1.
