Formula Errors

How to Return PENDING in Excel When J2 is COMPLETE and K2 is Blank

Muhammad Talha Oct 12, 2026 869 views

Question details

The user needs an Excel formula to output "PENDING" when cell J2 contains "COMPLETED" and cell K2 is blank, without disrupting the existing logic that returns "EXPIRED" or "COMPLETED" based on dates in cells L2, M2, and A2.

How to Write an Excel Formula to Return PENDING for Specific Cell Conditions

Product: Excel
Device & OS: not provided
Scenario: Adding a new condition to a complex nested IF formula for status tracking.
Observed behavior: Previous formula attempts overrode the original date-based logic, incorrectly returning "PENDING" even when date cells (L2 and M2) were populated and should have returned "COMPLETED".

Before you start

Review your existing formula logic to determine the correct hierarchy of conditions, as spreadsheet software evaluates nested IF statements sequentially from left to right.

Solution 1Recommended

Append the New Condition to the End of the Nested IF Formula

Place the new status check after the primary date conditions to ensure the original expiration and completion logic takes precedence.

In a nested IF statement, the first condition that evaluates to TRUE dictates the result. To prevent your new condition from overriding the original logic, the date checks must remain at the beginning of the formula.

Select the target cell

Click on the cell where you want the status (e.g., N2) to be displayed.

Establish the base date logic

Begin by typing your original formula that handles the dates: =IF(ISBLANK(L2),"",IF(AND(ISBLANK(M2),A2-L2>28),"EXPIRED",IF(ISNUMBER(M2),"COMPLETED", [Insert Next Condition Here])))

Add the new condition

Replace the final FALSE argument placeholder with your new requirement using the AND function: IF(AND(J2="COMPLETED",K2=""),"PENDING","PENDING").

Combine and apply the formula

Combine them into the final formula: =IF(ISBLANK(L2),"",IF(AND(ISBLANK(M2),A2-L2>28),"EXPIRED",IF(ISNUMBER(M2),"COMPLETED",IF(AND(J2="COMPLETED",K2=""),"PENDING","PENDING")))) and press Enter.

Append the New Condition to the End of the Nested IF Formula

Adjusting the Final Default Value: In the provided formula, the final result defaults to "PENDING" if none of the preceding conditions are met. You can change this last "PENDING" to "" (blank) or another status word based on your workflow needs.

Advanced Spreadsheet Tool

Easily Manage Complex Formulas with WPS Spreadsheet

Write, troubleshoot, and evaluate complex nested IF formulas effortlessly in WPS Office. It provides robust error-checking, syntax highlighting, and full compatibility with Microsoft Excel functions.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your status tracking table.
2. Select the formula cell: Click the cell that requires the complex condition.
3. Use the formula builder: Navigate to the Formula tab and select 'Insert Function' to build your logic step-by-step with guided prompts.
4. Apply and drag: Press Enter to apply the formula, then drag the fill handle down to apply the logic to all rows in your dataset.

Fully compatible with Excel's IF, AND, and ISBLANK functionsColor-coded parenthesis matching to prevent syntax and nesting errorsBuilt-in formula evaluation tool to step through complex logical testsCompletely free and lightweight alternative to heavy spreadsheet applications

microsoft office alternative - wps office

Frequently Asked Questions

Why does my nested IF formula return the wrong status?

Spreadsheets evaluate nested IF formulas sequentially from left to right. Once a condition is met (evaluates to TRUE), the formula stops checking the remaining conditions. Ensure your most important or specific overriding conditions are placed first in the formula string.

How do I check if a cell is blank in an Excel formula?

You can use the built-in ISBLANK() function, such as ISBLANK(K2), or use double quotation marks, like K2="". Both methods effectively evaluate whether a cell contains no data and can be used interchangeably in most logical tests.

Can I combine multiple conditions in a single IF statement?

Yes, you can use the AND() or OR() functions inside the logical test portion of an IF statement. For example, IF(AND(J2="COMPLETED", K2=""), "PENDING", "OTHER") requires both conditions to be strictly true to return the "PENDING" status.

Excel & Data Problems