WPS Spreadsheet IF Formula for Compliant, Pending, Expired, or Not Completed Status
Question details
The user needs a spreadsheet formula that returns a status based on three date cells and the current date.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet uses cells L4, P4, and V4 to track primary and secondary dates. The user wants to classify each row as Not Completed, Compliant, Pending, or Expired.
- Observed behavior
- The desired status depends on whether all three cells are blank, whether L4 is today or later, whether P4 or V4 is today or later, or whether none of those conditions is met.
Confirm that L4, P4, and V4 are the correct worksheet references and contain real date values rather than text. Check that the cells use a date format so comparisons with TODAY() work correctly.
Use an IFS formula to return the four date-based statuses
Use this solution when the three tracking cells may be blank or contain dates, and the tests must run in a specific priority order.
IFS evaluates conditions from left to right. Checking the all-blank condition first ensures that an empty row is labeled Not Completed before any date comparison is attempted.
Open the worksheet in WPS Spreadsheet and click the cell where the status should appear, such as W4.
Type =IFS(AND(L4="",P4="",V4=""),"Not Completed",L4>=TODAY(),"Compliant",OR(P4>=TODAY(),V4>=TODAY()),"Pending",TRUE,"Expired") in the selected cell.
Press Enter or click the checkmark in the formula bar to calculate the status.
Select the completed formula cell and drag its fill handle down, or use Home > Fill > Down, to apply the relative references to other rows.
Use ISBLANK when only truly empty cells should count as incomplete
Use this alternative when cells containing formulas that return an empty string should not be treated as physically blank.
Build date-based compliance statuses in WPS Spreadsheet
WPS Spreadsheet lets you enter the IFS formula, compare dates with TODAY(), and fill the result down a worksheet to classify records as Not Completed, Compliant, Pending, or Expired.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the date columns.
- 2. Enter the status formula: Click the target status cell and enter the IFS formula using the correct row references, such as L4, P4, and V4.
- 3. Check the result: Press Enter and verify the displayed status against the dates and blank cells in that row.
- 4. Fill down the column: Drag the fill handle or use Home > Fill > Down to apply the formula to additional records.
Frequently Asked Questions
Why must the blank condition come first in the IFS formula?
IFS returns the result for the first condition that evaluates to TRUE. Checking AND(L4="",P4="",V4="") first ensures rows with no dates return Not Completed instead of being evaluated as another status.
What does the TODAY() function do in this status formula?
TODAY() returns the current date. The comparisons L4>=TODAY(), P4>=TODAY(), and V4>=TODAY() identify dates that are today or in the future.
When will the formula return Pending?
It returns Pending when the row is not entirely blank, L4 is not today or later, and either P4 or V4 is today or later.
Why might a date comparison return the wrong status?
The cell may contain text that only looks like a date. Select the cell and use Home > Number Format to apply a date format, then re-enter or convert the value if necessary.
Can I use this formula with different columns or rows?
Yes. Replace L4, P4, and V4 with the correct references for your worksheet. When you fill the formula down, WPS Spreadsheet automatically adjusts relative row references.
