How to Display Weekday Name and Number from a Date in Excel
Question details
The user wants to format date cells in Excel to display the weekday name alongside its corresponding numerical value without showing the year.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting and formatting specific day information from adjacent date cells for reporting or scheduling purposes.
- Observed behavior
- The goal is a single cell displaying the full weekday name followed by its numerical representation (e.g., 'Monday 1').
Ensure your source cells are formatted as valid dates in Excel, otherwise the date formulas will return a #VALUE! error.
Combine TEXT and WEEKDAY Functions
Use the TEXT function to extract the day name and the WEEKDAY function to get the day number, combining them with an ampersand (&).
By using an ampersand (&), you can concatenate the text output of two different functions into a single cell. The TEXT function converts the date to a specific format like 'dddd' for the full day name, while the WEEKDAY function returns a number from 1 to 7 corresponding to the day of the week.
Click on a blank cell adjacent to the date you want to convert.
Type the formula =TEXT(A1,"dddd")&" "&WEEKDAY(A1) into the formula bar, assuming your source date is in cell A1.
Press Enter to display the result (e.g., 'Sunday 1'). If your work week starts on Monday, modify the formula to =TEXT(A1,"dddd")&" "&WEEKDAY(A1,2) so that Monday is numbered 1 and Sunday is 7.
Use WPS Spreadsheet for Advanced Date Formatting
WPS Spreadsheet fully supports standard Excel formulas including TEXT and WEEKDAY, making date manipulation quick and seamless. Process your data flawlessly with a familiar interface.
- 1. Open your file: Launch WPS Office and open your spreadsheet containing the dates.
- 2. Enter the combination formula: Select an empty cell next to your date and input the =TEXT(A1,"dddd")&" "&WEEKDAY(A1) formula.
- 3. Fill down the column: Press Enter, then click and drag the fill handle at the bottom-right of the cell to apply the formula to the rest of your data.
Frequently Asked Questions
How do I display a short weekday name (e.g., 'Mon') instead of the full name?
In the TEXT function, change the format code from 'dddd' to 'ddd'. Your modified formula will look like this: =TEXT(A1,"ddd")&" "&WEEKDAY(A1).
Can I display the date and the weekday number together without the year?
Yes, you can modify the TEXT format string. To show the month and day alongside the weekday number, use a formula like =TEXT(A1,"mm/dd dddd")&" - Day "&WEEKDAY(A1).
Why is my formula returning an error instead of the weekday?
This typically happens if the source cell is formatted as text rather than a serial date value. Select the original cell, press Ctrl+1 to open the Format Cells dialog, and ensure it contains a valid Date format.
How can I set the week to start on a different day, like Tuesday?
The WEEKDAY function supports various return types. You can use =WEEKDAY(A1, 12) where Tuesday is day 1 and Monday is day 7. When typing the function, an auto-complete tooltip will list all available return type codes.
