How to Use Excel Formula to Set Completed, Open, or Pending Status
Question details
The user needs a spreadsheet formula to automatically determine and display a task's status based on the presence of dates or numerical values in adjacent columns.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Tracking project or task progress where the status depends on multiple conditions: whether a completion date is entered, whether an initial value exists without a date, or if both fields are blank.
- Observed behavior
- The goal is to display "Completed" when column B has a date, "Open" when column B is blank but column A is greater than zero, and "Pending" when both cells are blank.
Ensure that your data columns are properly structured, verifying that dates in column B are recognized as actual non-blank values by the spreadsheet, and column A contains numerical data.
Use Nested IF Formula Checking for Non-Blank Cells
This method is the most reliable approach, as it directly evaluates whether the completion date cell is not empty before checking secondary conditions.
The IF function in Excel evaluates a condition and returns one value if true, and another if false. By nesting IF functions, you can test multiple conditions sequentially. This solution evaluates column B first, and if it is blank, it moves on to evaluate column A.
Click on the cell where you want the status text to appear (for example, C2).
Type the formula =IF(B2<>"","Completed",IF(A2>0,"Open","Pending")) into the formula bar.
Press Enter to apply the formula. Then, click and drag the fill handle at the bottom-right corner of the cell down to apply it to the remaining rows in your tracking sheet.
Use Nested IF Formula Checking for Values Greater Than Zero
If your completion date column strictly contains numerical values or properly formatted dates (which are stored as numbers), you can check if the value is greater than zero.
Automate Task Tracking with WPS Spreadsheet
WPS Office provides a powerful, highly compatible spreadsheet tool that fully supports nested IF functions and complex logical formulas. You can easily manage project statuses, analyze data, and streamline your workflow with its intuitive interface.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your task tracking data.
- 2. Select the status cell: Click on the specific cell where the automated status should be displayed.
- 3. Type the formula: Input your nested IF formula, utilizing the built-in formula hints provided by WPS for guidance.
- 4. Drag to apply: Press Enter to confirm, then drag the fill handle down to apply the logic to your entire list seamlessly.
Frequently Asked Questions
Can I add a fourth status like "Overdue" to this formula?
Yes, you can add another nested IF statement to accommodate more conditions. For example, you can check if today's date is past a deadline column before evaluating the Open or Pending conditions by inserting IF(TODAY()>D2,"Overdue",...) into the logic string.
Why does my formula return an error when I copy it down?
Ensure your cell references are correctly aligned. By default, copying a formula down uses relative references (e.g., B2 shifts to B3). If you are receiving errors, verify that you are not accidentally referencing a static header cell. If you need to lock a reference, use absolute references like $A$2.
How do I make the status text appear in different colors automatically?
You can use the Conditional Formatting feature to color-code your statuses. Select your entire status column, navigate to the Home tab, click Conditional Formatting > Highlight Cells Rules > Equal To, and assign specific background or text colors for "Completed", "Open", and "Pending".
