How to Display Weekday and Date in Excel Worksheet Headers
Question details
The user wants to automatically display the correct weekday and date, including ordinal suffixes, in the headers or merged cells of daily worksheets within a weekly workbook.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up a weekly workbook where each sheet corresponds to a day of the week (Monday through Friday), and the header needs to dynamically update to show that specific day's date for the current week.
- Observed behavior
- The user needs a dynamic formula that calculates the correct date for each weekday based on the current date, along with formatting techniques to display it clearly, such as 'Monday 27th December 2021'.
Ensure you have your daily worksheets created (e.g., Monday through Friday) and identify the specific header cell or merged cell block on each sheet where you want the dynamic date to appear.
Use TODAY and WEEKDAY Functions for Daily Dates
Use a combination of TODAY() and WEEKDAY() functions to dynamically calculate the exact date for Monday through Friday of the current week, then apply a custom format.
This method calculates the start of the current week by subtracting the current weekday from today's date, then adding an offset number to land on the specific day you need. The results update automatically whenever the workbook is opened.
Navigate to the Monday worksheet and select the target header cell. Type the formula `=TODAY()+2-WEEKDAY(TODAY())` and press Enter. This dynamically returns the date for Monday of the current week.
Repeat this process on the other daily worksheets by increasing the offset number. Use `=TODAY()+3-WEEKDAY(TODAY())` for Tuesday, `+4` for Wednesday, `+5` for Thursday, and `+6` for Friday.
Select the cell with the formula, right-click, and choose 'Format Cells'. Go to the 'Number' tab, select 'Custom', and enter `dddd mmmm d yyyy` in the Type field. Click OK to display the date as 'Monday December 27 2021'.
Display Ordinal Suffixes Using TEXT and CHOOSE Functions
Excel's standard custom formatting does not support ordinal suffixes (st, nd, rd, th). You must use a text manipulation formula to append the correct suffix to the date.
Dynamically Calculate Weekdays and Dates in WPS Spreadsheet
WPS Spreadsheet fully supports Excel's advanced date functions including TODAY, WEEKDAY, CHOOSE, and TEXT. You can easily set up dynamic weekly workbooks, format headers beautifully, and manage your schedule efficiently with complete formula compatibility.
- 1. Open your workbook: Launch WPS Spreadsheet and open the weekly workbook where you need dynamic date headers.
- 2. Enter the weekday formula: Select the header cell on your daily sheet and input the formula `=TODAY()+2-WEEKDAY(TODAY())`.
- 3. Format the date: Press Ctrl+1 to open the Format Cells dialog, click Custom, and type `dddd mmmm d yyyy` to finalize the display format.
Frequently Asked Questions
Why does my date formula show a 5-digit number instead of a date?
Spreadsheet software stores dates as sequential serial numbers for calculation purposes. If your formula outputs a number like 44557, right-click the cell, select 'Format Cells', and change the formatting from 'General' to 'Date' or apply a custom date format.
How do I change the start of the week for the WEEKDAY function?
By default, the WEEKDAY function treats Sunday as day 1 and Saturday as day 7. You can add a return_type argument to change this. For example, using WEEKDAY(TODAY(), 2) will set Monday as day 1. Note that if you change this setting, you will need to adjust the offset numbers (+2, +3, etc.) in your formulas accordingly.
Can I prevent the dates from updating when I open the file next week?
Yes. The TODAY() function is volatile and updates based on your system clock. If you want to keep the current week's dates permanently, select the cells containing the date formulas, press Ctrl+C to copy them, right-click the same cells, and select 'Paste as Values'. This replaces the formula with static text.
