Formula Errors

How to Use Excel IF Formula for Pending, Expired, and Completed Statuses

Steve K Oct 12, 2026 877 views

Question details

The user needs an Excel formula to automatically return statuses such as PENDING, EXPIRED, or COMPLETED based on specific date conditions, blank cells, and whether a completion date is entered.

How to Use Excel IF Formula for Pending, Expired, and Completed Statuses

Product: Excel
Device & OS: not provided
Scenario: Tracking project or task statuses dynamically using start dates, completion dates, and elapsed time.
Observed behavior: The user successfully implemented a nested IF formula but noticed that when the initial start date cell is blank, the formula incorrectly calculates the time difference and returns 'EXPIRED' instead of remaining blank.

Before you start

Ensure your columns containing start dates and completion dates are properly formatted as 'Date' rather than 'Text' so that the formula can accurately calculate the number of days passed.

Solution 1Recommended

Use a Nested IF and AND Formula for Basic Status Tracking

Combine the IF, AND, ISBLANK, and TODAY functions to dynamically generate a status based on the passage of time and whether a completion date has been entered.

This formula checks two primary conditions: whether the task is past its due date (e.g., 28 days from the start date) without completion, and whether a completion date is logged.

Select the status cell

Click on the cell where you want the status text to appear, for example, cell I2.

Enter the nested formula

Type the following formula: =IF(AND(ISBLANK(H2),TODAY()-G2>28),"EXPIRED",IF(ISNUMBER(H2),"COMPLETED","PENDING"))

Adjust cell references

Change 'G2' to your start date cell and 'H2' to your completion date cell. The number '28' represents the expiration threshold in days; adjust it as needed.

Apply to the entire column

Press Enter, then click and drag the fill handle at the bottom-right corner of the cell down to apply the formula to other rows.

Use a Nested IF and AND Formula for Basic Status Tracking

Dynamic Updates: The cell will now automatically update to EXPIRED if the designated number of days passes without a valid completion date.

Solution 2

Prevent Incorrect 'EXPIRED' Status When the Base Date is Blank

If your start date cell is empty, the formula might incorrectly return 'EXPIRED'. Add an initial IF statement to force the status to remain blank until a date is entered.

Track Statuses Easily in WPS Spreadsheet

Manage Complex Project Statuses with WPS Spreadsheet

You can effortlessly build and manage complex nested IF formulas in WPS Spreadsheet. It features full compatibility with standard spreadsheet formulas, ensuring your status tracking workflows transition seamlessly.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the file where you need to track statuses.
2. Enter the IF formula: Click on your status cell and type the exact nested IF formula used in Excel.
3. Fill the formula down: Press Enter, then double-click the fill handle to instantly apply the formula to all rows.
4. Apply color coding: Go to the Home tab, click 'Conditional Formatting', and set rules to make 'EXPIRED' text turn red automatically.

Fully compatible with Microsoft Excel formulas, functions, and formattingEasily apply Conditional Formatting to color-code your PENDING and EXPIRED statusesFree, fast, and lightweight spreadsheet solution for everyday project management

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the TODAY() function update my statuses automatically?

The TODAY() function updates whenever the workbook recalculates (like when you open the file or modify a cell). If your statuses aren't updating automatically, press F9 on your keyboard to manually recalculate, or ensure that 'Automatic' calculation is enabled in the Formulas tab under Calculation Options.

How can I change the expiration threshold to a different number of days?

Locate the portion of your formula that calculates the date difference, such as TODAY()-G2>28. Simply replace the number 28 with your desired number of days, like 14 or 30, and press Enter.

Can I color-code the Pending, Expired, and Completed statuses?

Yes. Select the entire column containing your formulas, go to the Home tab, and select Conditional Formatting > Highlight Cells Rules > Text that Contains. You can assign specific colors (e.g., Red for EXPIRED, Green for COMPLETED) to make tracking easier.

Excel & Data Problems