WPS Spreadsheet: Use an IF Formula to Show Task Completion Status
Question details
The user needs an IF formula that shows a task's completion status based on its closing date.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Task closing dates are stored in column C, and the status formula needs to begin in cell E2 and be filled down the column.
- Observed behavior
- The desired result is a blank when no closing date exists, Completed when the date is today or earlier, and Not Completed when the date is in the future.
Confirm that the closing dates in column C are recognized as spreadsheet dates rather than text. Leave the status column beginning at E2 so the cell references match the formula.
Use a nested IF formula for date-based task status
Use this solution when you want WPS Spreadsheet to automatically label tasks as Completed, Not Completed, or blank from their closing dates.
The outer IF checks whether a closing date exists. The inner IF compares the date with TODAY(), marking dates that are today or earlier as completed and future dates as not completed.
Open the worksheet in WPS Spreadsheet and select cell E2, or the first cell where the status should appear.
In the formula bar or directly in E2, enter =IF(C2<>"",IF(C2<=TODAY(),"Completed","Not Completed"),"") and press Enter.
Verify that E2 is blank when C2 has no date, shows Completed for today or an earlier date, and shows Not Completed for a future date.
Select E2 and drag its fill handle down the status column, or copy E2 and paste it into the remaining rows.
Create automatic task status labels in WPS Spreadsheet
WPS Spreadsheet lets you enter the nested IF formula, compare closing dates with TODAY(), and fill the result down a task list using a familiar spreadsheet interface. It supports common Excel workbook formats for easy editing and sharing.
- 1. Open your workbook: Launch WPS Office, open your spreadsheet, and locate the task closing-date column and status column.
- 2. Enter the IF formula: Select E2 and enter =IF(C2<>"",IF(C2<=TODAY(),"Completed","Not Completed"),"") in the formula bar.
- 3. Apply the formula: Press Enter, confirm the displayed status, then drag the fill handle down the remaining rows.
- 4. Save the workbook: Use File > Save or press Ctrl+S to save the updated task status sheet in its existing format.
Frequently Asked Questions
What formula shows a blank when the task has no closing date?
In WPS Spreadsheet, enter =IF(C2<>"",IF(C2<=TODAY(),"Completed","Not Completed"),"") in E2. The final empty text argument returns a blank when C2 is empty.
How does the formula decide whether a task is completed?
The inner IF tests whether the date in C2 is less than or equal to TODAY(). Dates today or earlier return Completed, while future dates return Not Completed.
How can I apply the formula to all task rows?
After entering the formula in E2, select the cell and drag its fill handle down the status column. WPS Spreadsheet adjusts C2 to C3, C4, and so on for each row.
Why does a closing date appear to have the wrong status?
Check that the value in column C is a valid date recognized by WPS Spreadsheet, not text that only looks like a date. You can also verify the cell's date format from the Home tab.
Will the status update when the date changes?
Yes. The TODAY() function uses the current date, so a future date can automatically change from Not Completed to Completed when the workbook recalculates after that date arrives.
