How to Stop a Working-Day Count in WPS Spreadsheets When a Criterion Is Met
Question details
The user needs a WPS Spreadsheets/Excel formula that counts working days from TODAY and stops changing when a criterion cell contains Yes, while retaining the currently displayed result.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is tracking working days between a start date and today and wants the total to freeze once the item is marked as completed.
- Observed behavior
- The TODAY-based formula continues to recalculate every day, so the displayed working-day count changes even after the criterion is met.
Before editing the formula, locate the criterion cell, such as E1, the start-date cell, such as C1, and add a separate completion-date cell where WPS Spreadsheets can store the fixed end date. This prevents the formula from depending on TODAY after the task is marked complete.
Use a Fixed Completion-Date Cell with an IF and NETWORKDAYS Formula
Best for most users who want a stable count without VBA: enter the actual completion date when the criterion is met, and calculate D1 from that fixed date instead of TODAY.
A regular worksheet formula cannot retain its previous result after recalculation. The safest design is to record the completion date in a separate cell and use that date once the criterion is met.
In WPS Spreadsheets, add a new column such as F and label it Completion Date. When E1 becomes Yes, type the actual completion date in F1.
Select D1 and enter =IF(E1="Yes",NETWORKDAYS(C1,F1),NETWORKDAYS(C1,TODAY())). This calculates working days from the fixed date if E1 is Yes, otherwise calculates from today.
Press Enter, then apply General or Number formatting to D1. Drag the fill handle down if the same rule is needed for other rows.
Freeze the Current Value with Iterative Calculations
Can be used only if you understand workbook calculation risks and are willing to enable iterative calculation; normally the fixed-date method above is safer.
Freeze Working-Day Counts in WPS Spreadsheets with a Fixed Completion Date
Use WPS Spreadsheets to replace a volatile TODAY-only formula with a fixed completion-date cell and an IF/NETWORKDAYS formula, so the working-day total stops changing once the criterion is met.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the .xlsx file, and select the sheet that contains the working-day formula and criterion cell.
- 2. Add a completion-date column: Insert a new column next to the criterion, for example column F, and label it Completion Date so each row can store the fixed end date.
- 3. Enter the conditional formula: In D1, type =IF(E1="Yes",NETWORKDAYS(C1,F1),NETWORKDAYS(C1,TODAY())). Adjust the cell references to match your sheet.
- 4. Fill the formula down: Drag the fill handle from D1 down through the remaining data rows. Enter a completion date in column F for any row marked Yes.
Frequently Asked Questions
Why does my working-day count keep changing after I enter Yes?
Because the formula still contains TODAY(), which recalculates every time the workbook opens or recalculates. Use a fixed completion-date cell in the IF formula so the end date is static once the criterion is met.
Can an Excel or WPS formula remember its previous value without VBA?
No. A regular worksheet formula cannot store a past result. You need a fixed date cell, iterative calculation, or a VBA/macro solution.
Which WPS Spreadsheets function counts working days?
Use NETWORKDAYS(start_date,end_date) or NETWORKDAYS.INTL for custom weekends. The example uses NETWORKDAYS(C1,F1) when the criterion is met.
What should I do if I see a circular reference error?
If you used iterative calculation, open Options or Calculation settings and confirm iterative calculation is enabled. Otherwise, replace the self-referencing formula with the fixed completion-date method to avoid the error.
Can WPS Spreadsheets open Excel workbooks with TODAY and NETWORKDAYS?
Yes, WPS Spreadsheets supports these Excel functions and saves in .xlsx format, so the workbook remains compatible with Microsoft Excel.
