Function Problems

WPS Spreadsheets Formula to Move Friday Dates Back to Thursday

Kushani Nimanthika Nov 12, 2026 869 views

Question details

The user needs a formula that returns the date from B97 unchanged on every day except Friday, when it should return the preceding Thursday.

Product: WPS Spreadsheets
Device & OS: not provided
Scenario: A worksheet uses the date in cell B97 and needs a calculated result in another cell that shifts Friday dates back by one day.
Observed behavior: The desired result is Thursday when B97 contains a Friday date, while dates from all other weekdays remain unchanged. The formula must not modify B97 itself.

Before you start

Confirm that B97 contains a valid spreadsheet date and select a separate result cell, because the formula returns a new value rather than changing B97 directly. Format the result cell as a date if it displays a serial number.

Solution 1Recommended

Use an IF and WEEKDAY formula to shift Friday to Thursday

Use this formula when only Friday dates should move back one day and all other dates should remain unchanged.

The return type 2 in WEEKDAY numbers Monday as 1 through Sunday as 7, so Friday is represented by 5. The formula checks B97, subtracts one day only when the result is Friday, and otherwise returns the original date.

Select the result cell

Click the cell where you want the adjusted date to appear, such as C97, rather than selecting B97 itself.

Enter the formula

Type =IF(WEEKDAY(B97,2)=5,B97-1,B97) in the formula bar or directly into the selected cell, then press Enter.

Check the result

If B97 is a Friday, the result cell displays the preceding Thursday; if B97 is any other weekday, it displays the same date as B97.

Format the result as a date

If the result appears as a number, select the result cell, open the Home tab, and choose a date format from the Number Format controls.

B97 remains unchanged: This formula calculates the adjusted date in the result cell and does not overwrite the original value in B97.

Calculate Adjusted Weekday Dates in WPS Spreadsheets

WPS Spreadsheets lets you apply the IF and WEEKDAY functions to shift Friday dates to Thursday while preserving every other date. Enter the formula in a separate cell and format the result as a date for a clear, reusable worksheet calculation.

1. Open your workbook: Launch WPS Office, choose Spreadsheets, and open the workbook containing the date in B97.
2. Choose an output cell: Select a cell beside the source date where the adjusted date should appear.
3. Enter the date formula: Enter =IF(WEEKDAY(B97,2)=5,B97-1,B97) and press Enter.
4. Verify and format the result: Test a Friday value in B97 and use the Home tab's Number Format controls to display the calculated value as a date.

Automatically moves Friday dates back by one day without changing the source dateKeeps dates from Monday through Thursday and Saturday through Sunday unchangedSupports familiar Excel formulas and XLSX workbook compatibilityProvides a lightweight spreadsheet interface for building and checking date calculations

microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula use WEEKDAY(B97,2)=5?

The second argument, 2, makes Monday equal 1, Tuesday equal 2, and Friday equal 5. Therefore, the IF condition identifies Friday dates specifically.

Will this formula change the date in B97?

No. The formula only reads B97 and places the calculated result in another cell. B97 remains unchanged.

What happens when B97 contains a Monday, Tuesday, or another non-Friday date?

The IF condition is false, so the formula returns B97 without subtracting a day.

Why does the result show a number instead of a date?

Spreadsheets store dates as serial numbers. Select the result cell, open the Home tab, and apply a date format from the Number Format controls.

Excel & Data Problems