WPS Spreadsheet Formula to Adjust Friday Dates Back to Thursday
Question details
The user needs formulas that move dates falling on Friday back by one day while maintaining the intended spacing between an earlier and a later date.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- An earlier date is in H157 and a later date is in H165. The later date is eight days after the starting date, and Friday dates must be adjusted to Thursday.
- Observed behavior
- Friday dates remain one day later than the required schedule unless the formulas subtract one day when WEEKDAY returns 6.
Confirm that H157 and H165 contain real spreadsheet dates rather than text, and check that both cells use a date format. In WPS Spreadsheet, select the cells and use Home > Number Format if the results display as serial numbers.
Use WEEKDAY formulas to move Friday dates back by one day
Use these formulas when the dates are stored in H157 and H165 and Friday must be treated as the previous Thursday.
With the default WEEKDAY numbering, Sunday is 1 and Friday is 6. Subtracting the TRUE/FALSE result converts Friday to a one-day adjustment while leaving other weekdays unchanged.
Select the earlier-date result cell and enter =H157-(WEEKDAY(H157)=6). If the earlier date must also respond to the later date being Friday, use =H157-(WEEKDAY(H157)=6)-(WEEKDAY(H165)=6).
Select the later-date result cell and enter =H165-(WEEKDAY(H165)=6). This subtracts one day only when H165 falls on Friday.
Select both result cells, open Home > Number Format, and choose a date format such as Short Date so the adjusted values display as calendar dates.
Change or review H157 and H165, then confirm that any Friday result displays as Thursday while dates on other weekdays remain unchanged.
Use an IF formula when you want the Friday rule to be explicit
Use this alternative when you prefer a formula that clearly shows the Friday condition instead of relying on TRUE/FALSE arithmetic.
Adjust Friday dates accurately in WPS Spreadsheet
WPS Spreadsheet supports WEEKDAY, IF, and date arithmetic formulas, allowing you to shift Friday dates to Thursday and preserve scheduling rules without manual edits.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the date cells.
- 2. Enter the date formulas: Select the result cells and enter =H157-(WEEKDAY(H157)=6)-(WEEKDAY(H165)=6) and =H165-(WEEKDAY(H165)=6) as appropriate.
- 3. Format the results: Select the formula cells and choose Home > Number Format > Short Date.
- 4. Check the schedule: Review Friday and non-Friday examples to confirm the required spacing and Thursday adjustment.
Frequently Asked Questions
Why does the formula use WEEKDAY(...)=6?
With the default WEEKDAY setting, Sunday is 1 and Friday is 6. The comparison returns TRUE on Friday, which spreadsheet arithmetic treats as 1, so the formula subtracts one day.
What formula adjusts only the later date in H165?
Enter =H165-(WEEKDAY(H165)=6) in the later-date result cell. It subtracts one day when H165 is Friday and leaves other weekdays unchanged.
Why does the earlier-date formula also check H165?
The extra -(WEEKDAY(H165)=6) term lets the earlier result shift when the later date is Friday, helping preserve the required relationship between the two dates.
What should I do if the formula displays a number instead of a date?
Select the result cell, open Home > Number Format, and choose Short Date or another date format. Also verify that the source cells contain actual dates, not text that only looks like dates.
