Excel Formula to Subtract Calendar Days and Move Weekend Dates to Friday in WPS Spreadsheet
Question details
How to subtract a specified number of calendar days from a date and automatically move weekend results to Friday in WPS Spreadsheet.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user has a starting date in a cell and needs to calculate an earlier date by a set number of calendar days, with Saturday or Sunday results shifted back to Friday.
- Observed behavior
- The formula should return an earlier date and move Saturday or Sunday results to the preceding Friday.
Make sure the starting date is entered as a valid date or date serial value in WPS Spreadsheet, and confirm the number of calendar days to subtract.
Use the WORKDAY formula to subtract calendar days and move weekend dates to Friday
Use this method when you want one formula to subtract the desired number of calendar days and automatically shift Saturday or Sunday results to Friday.
The WORKDAY function treats Saturday and Sunday as non-working days. The formula =WORKDAY(A1-9,-1) subtracts 10 calendar days from the date in A1: it first subtracts 9 calendar days, then steps one workday backward, producing the preceding Friday when the calendar result lands on a weekend.
Open WPS Spreadsheet and click the cell where you want the adjusted date to appear.
Type =WORKDAY(A1-9,-1) in the formula bar, replacing A1 with your starting date cell and 9 with one less than the number of calendar days to subtract.
Press Enter to calculate the formula and display the adjusted date.
If the result looks like a serial number, select the cell, go to the Home tab, open Number Format, and choose Short Date or Long Date.
Calculate Weekend-Safe Dates in WPS Spreadsheet
Use the built-in WORKDAY function in WPS Spreadsheet to subtract calendar days and automatically adjust weekend results to Friday, with full Excel formula compatibility.
- 1. Open your workbook: Launch WPS Office, open WPS Spreadsheet, and load the .xlsx or .xls file that contains your dates.
- 2. Click an empty result cell: Select the cell where the adjusted date should be calculated.
- 3. Type the WORKDAY formula: Enter =WORKDAY(A1-9,-1), replacing A1 with your date cell and 9 with one less than the number of calendar days to subtract.
- 4. Format the result as a date: Go to Home > Number Format and select Short Date or Long Date so the result appears as a date.
- 5. Check weekend fallback: Confirm that any result originally on Saturday or Sunday now shows the preceding Friday.
Frequently Asked Questions
Why does the formula use A1-9 instead of A1-10?
WORKDAY then subtracts one workday. This combination subtracts the total number of calendar days and lets WORKDAY handle weekend fallback. Replace 9 with N-1, where N is the total calendar days to subtract.
Does WORKDAY automatically treat Saturday and Sunday as weekend days in WPS Spreadsheet?
Yes. By default, WORKDAY uses a Monday-to-Friday work week, so Saturday and Sunday are treated as non-working days and a weekend result is moved to the preceding workday.
What if the formula returns a number instead of a date?
Select the result cell, go to Home > Number Format, and choose Short Date or Long Date. The number is the underlying date serial value.
Can I use this formula with a different starting cell or number of days?
Yes. Replace A1 with your date cell and 9 with one less than the total days. For example, to subtract 15 days use =WORKDAY(A1-14,-1).
