WPS Spreadsheet: Prevent Blank Maintenance Dates from Showing as Overdue
Question details
Prevent a maintenance schedule from calculating or formatting a due date when its completion date is blank.

- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A maintenance schedule calculates the due date by adding a frequency to a completion date and uses conditional formatting to identify overdue tasks.
- Observed behavior
- Blank completion dates can produce unwanted due-date values or cause rows to appear overdue.
Confirm the exact names or cell references for the Completed, Frequency (Days), and Due columns. Make sure completed dates are stored as valid spreadsheet dates and frequency values are numeric.
Return a blank due date until a completion date exists
Use this formula in the Due column to calculate a date only after the maintenance task has a valid completion date.
The IF test prevents WPS Spreadsheet from adding the frequency to an empty completion field. Structured references work in a WPS table; for a regular range, replace them with the relevant cell references.
Open the worksheet in WPS Spreadsheet and click the first cell in the Due column that should calculate a maintenance date.
Type =IF([@Completed]="","",[@Completed]+[@[Frequency (Days)]]) in the formula bar, then press Enter.
Use the fill handle or copy and paste the formula through the remaining Due cells in the maintenance table.
Exclude blank due dates from overdue conditional formatting
Update the overdue rule when blank Due cells are still being highlighted by conditional formatting.
Build Reliable Maintenance Date Formulas in WPS Spreadsheet
WPS Spreadsheet lets you create blank-safe IF formulas, calculate maintenance due dates, and control overdue highlighting through formula-based conditional formatting.
- 1. Open the maintenance workbook: Launch WPS Office, choose Spreadsheet, and open your maintenance schedule.
- 2. Add the due-date formula: Select the first Due cell and enter =IF([@Completed]="","",[@Completed]+[@[Frequency (Days)]]) or the equivalent cell-reference formula.
- 3. Configure overdue formatting: Go to Home > Conditional Formatting > Manage Rules and add or edit a formula using =AND($D2<>"",$D2<TODAY()).
- 4. Review and save the schedule: Check blank and completed rows, then use File > Save or Save As to store the updated workbook.

Frequently Asked Questions
Why does a blank completion date create an overdue maintenance date?
A blank date may be treated as zero or as an earlier date when a frequency is added or compared with TODAY(). Test the completion cell first with =IF([@Completed]="","",[@Completed]+[@[Frequency (Days)]]) so no date is returned while it is blank.
How do I stop conditional formatting from highlighting blank Due cells?
Edit the overdue rule under Home > Conditional Formatting > Manage Rules and use =AND($D2<>"",$D2<TODAY()), replacing D with the Due column. The first condition excludes blank due dates.
What should I do if my data is not formatted as a table?
Replace structured references with ordinary cell references. For example, if the completion date is in B2 and frequency is in C2, use =IF(B2="","",B2+C2), then fill the formula down.
Can the formula handle a missing frequency value?
The formula prevents calculation when Completed is blank, but a missing or nonnumeric frequency can still cause an error. Validate the Frequency (Days) cells or add an additional test such as ISNUMBER before adding the frequency.
Will this maintenance schedule work with Excel files?
Yes. WPS Spreadsheet supports Microsoft Excel .xlsx files, including standard IF formulas and formula-based conditional formatting. Review structured table references after opening files created in another spreadsheet application.




