How to Auto-Fill Month and Day from a Week-Ending Date in WPS Office Spreadsheets
Question details
The user needs to automatically generate daily dates from a week-ending date in a spreadsheet and display them as month and day while returning real Excel-compatible dates.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A week-ending date is entered in cell M8, and the user wants to fill surrounding cells with the corresponding daily dates for that week.
- Observed behavior
- The existing formula returns only a one- or two-digit day number instead of a real date that can be formatted as month/day.
Open the workbook in WPS Office Spreadsheets and identify the cell that contains the week-ending date. Confirm whether the week-ending date is a Saturday or Sunday so you can choose the correct offset for the first daily date.
Calculate the First Daily Date from a Saturday Week-Ending Date
Use this method when the week-ending date in M8 is a Saturday and you need a real date for the Sunday that begins that week.
The formula tests whether M8 is blank, then subtracts six days from the week-ending date. Because the result is a real date, you can format it as mm/dd without losing the date value.
Click the cell where you want the Sunday date to appear, for example N8.
Type =IF(ISBLANK($M$8),"",$M$8-6) and press Enter.
With the result cell selected, press Ctrl+1, choose Custom, enter mm/dd, and click OK.
In the next cells use =IF(ISBLANK($M$8),"",$M$8-5) for Monday, =IF(ISBLANK($M$8),"",$M$8-4) for Tuesday, and continue through =IF(ISBLANK($M$8),"",$M$8) for Saturday.
Confirm that each cell shows a month/day value such as 03/02 while the formula bar still stores a full date.
Copy a Completed Weekly Formula Row Down for Multiple Week-Ending Dates
Use this after you have set up the Sunday-through-Saturday formulas in the first row and need to apply them to additional rows.
Create Automatic Weekday Dates in WPS Office Spreadsheets
In WPS Office Spreadsheets, use an IF and date subtraction formula to turn a week-ending date into real daily dates, then apply the mm/dd custom format to display the month and day. Excel workbooks open, edit, and save normally in WPS Office.
- 1. Open your workbook in WPS Office: Download and install WPS Office, then open the Excel workbook in WPS Spreadsheets.
- 2. Enter the week-ending date formula: Select the target cell and type =IF(ISBLANK($M$8),"",$M$8-6), then press Enter.
- 3. Apply the mm/dd custom format: Press Ctrl+1 to open Format Cells, choose Custom, enter mm/dd, and click OK.
- 4. Fill the remaining weekdays: Use =IF(ISBLANK($M$8),"",$M$8-5) through =IF(ISBLANK($M$8),"",$M$8) for Monday through Saturday, or copy the completed row down with relative references.
Frequently Asked Questions
Why does my formula return only a day number instead of a month and day?
The formula likely returns a real date, but the cell is formatted as a number. Select the cell, press Ctrl+1, choose Custom, enter mm/dd, and click OK to display the month and day.
What if my week-ending date is Sunday instead of Saturday?
If M8 is a Sunday and you need the Monday through Sunday dates, use =IF(ISBLANK($M$8),"",$M$8-6) for Monday, then use -5, -4, -3, -2, -1, and 0 for the remaining days. The offset depends on the first day of your weekly schedule.
How do I copy the calculation down for multiple rows?
Change $M$8 to $M8 in the first row, then select the completed weekday formulas and drag the fill handle down. Each row will reference its own week-ending date in column M.
Can I display the date as month/day but keep it usable in calculations?
Yes. The mm/dd custom number format changes only the display. The cell still stores a date serial value, so you can use it in other date calculations or formulas.
Why does the cell appear blank after entering the formula?
The IF(ISBLANK(...)) check returns an empty string when M8 is blank. Enter a week-ending date in M8, and the calculated date will appear.
