WPS Spreadsheet: Add Days and Move Weekend Dates to Monday
Question details
The user needs to add a specified number of calendar days to a starting date and move the resulting date to the following Monday when it falls on a weekend.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A starting date is stored in D2 and the number of calendar days to add is stored in C2.
- Observed behavior
- The calculated date may fall on Saturday or Sunday and needs to be adjusted to the next weekday.
Confirm that D2 contains a valid date and C2 contains a numeric number of calendar days. Format the formula cell as a date so the result displays correctly.
Use WEEKDAY to move weekend results to the following Monday
Use this formula when the calculation must add calendar days first and then adjust Saturday or Sunday to a weekday.
Use WEEKDAY with return type 2 so Monday is 1, Saturday is 6, and Sunday is 7. This makes the weekend test and adjustment unambiguous.
In WPS Spreadsheet, click the cell where you want the adjusted date to appear.
Enter =IF(WEEKDAY(D2+C2,2)>5,D2+C2+CHOOSE(WEEKDAY(D2+C2,2)-5,2,1),D2+C2) and press Enter.
The formula adds C2 calendar days to D2. If the result is Saturday, it adds two days; if it is Sunday, it adds one day to reach Monday.
If the result appears as a number, select the cell, open the Home tab, choose the Number Format menu, and select Date.
Use WORKDAY to return the next weekday
Use this shorter alternative when you want WPS Spreadsheet to treat the calculated date as a workday and exclude weekends automatically.
Calculate Adjusted Weekday Dates in WPS Spreadsheet
WPS Spreadsheet lets you combine date arithmetic with WEEKDAY, CHOOSE, or WORKDAY functions to add calendar days and shift weekend results to Monday. Its familiar formula interface makes it easy to review and reuse the calculation.
- 1. Open WPS Spreadsheet: Launch WPS Office and open an existing workbook or create a new spreadsheet.
- 2. Enter the source values: Place the starting date in D2 and the number of calendar days in C2.
- 3. Add the formula: Select the result cell and enter =IF(WEEKDAY(D2+C2,2)>5,D2+C2+CHOOSE(WEEKDAY(D2+C2,2)-5,2,1),D2+C2).
- 4. Format and verify: Apply a Date format from the Home tab and verify that weekend results are moved to Monday.
Frequently Asked Questions
Why should the formula use WEEKDAY with a return type of 2?
WEEKDAY(date,2) numbers Monday as 1 through Sunday as 7. That means values greater than 5 identify Saturday and Sunday consistently, allowing the formula to add two days to Saturday and one day to Sunday.
What formula moves a Saturday result to Monday and a Sunday result to Monday?
Use =IF(WEEKDAY(D2+C2,2)>5,D2+C2+CHOOSE(WEEKDAY(D2+C2,2)-5,2,1),D2+C2). The CHOOSE function returns 2 for Saturday and 1 for Sunday.
Can I use WORKDAY instead of IF and WEEKDAY?
Yes. Use =WORKDAY(D2+C2-1,1) when you only need to shift a calculated weekend date to the next weekday. Add a holiday range as the third argument if holidays should also be excluded.
Why does my original WEEKDAY formula adjust the wrong day?
If WEEKDAY is used without a return type, WPS Spreadsheet normally numbers Sunday as 1 and Saturday as 7. A condition such as WEEKDAY(date)>5 then does not match the intended Monday-through-Sunday numbering. Add ,2 to WEEKDAY or use the WORKDAY alternative.
