Function Problems

How to Create a Status Formula Based on Start and Discharge Dates

Tauseeq Magsi Sep 28, 2026 869 views

Question details

The user needs a spreadsheet formula that automatically determines a project or item status based on the presence of dates in the Start Date and Discharged Date columns.

How to Create a Status Formula Based on Start and Discharge Dates

Product: Spreadsheets
Device & OS: not provided
Scenario: Tracking the life cycle of a task or case where a status should dynamically update to Not Started, In Progress, or Completed depending on which date fields are filled.
Observed behavior: The goal is for a status column to automatically display the correct tracking stage without manual data entry, evaluating the discharge date first, followed by the start date.

Before you start

Verify that your Start Date and Discharged Date columns are correctly formatted as date values, and confirm the exact column headers used in your worksheet before building the formula.

Solution 1Recommended

Use a Nested IF Formula to Determine Status

A nested IF statement evaluates multiple conditions in sequence. It first checks if a discharge date is present to mark it as Completed, then falls back to check the start date to mark it as In Progress.

When dealing with sequential statuses, the order of your IF functions is critical. By checking the final stage (Discharged Date) first, you ensure that completed items are not mistakenly labeled as 'In Progress' just because they also have a start date.

Select the target cell

Click on the first empty cell in your 'Status' column where you want the automated result to appear.

Enter the nested IF formula

Type the formula: =IF([@[Discharged Date]]<>"","Completed",IF([@[Start Date]]<>"","In Progress","Not Started")). This structured reference formula works natively if your data is formatted as an Excel Table.

Adapt for standard cell references

If you are not using a formatted Table, replace the table references with standard cell references. For example, if your Start Date is in column B and Discharged Date is in column C, use: =IF(C2<>"","Completed",IF(B2<>"","In Progress","Not Started")).

Apply formula to the entire column

Press Enter to execute the formula, then click and drag the fill handle at the bottom right of the cell to apply the status formula down through all remaining rows.

Use a Nested IF Formula to Determine Status

Logic Sequence: Always test your final condition first in a nested IF statement. The formula stops checking as soon as it finds the first TRUE condition.

Solution 2

Troubleshooting Syntax and Reference Errors

If the formula returns an error or fails to update the status, it is usually caused by incorrect column names, formatting, or invalid references.

Seamless Spreadsheet Data Management

Automate Project Tracking with WPS Spreadsheet

WPS Spreadsheet makes it easy to build nested IF formulas, track project statuses, and manage complex date calculations efficiently with built-in error checking.

1. Open your tracking file: Launch WPS Spreadsheet and open the document containing your start and discharge dates.
2. Select the status column: Click on the cell where the status formula should be applied.
3. Insert the logic formula: Paste your nested IF formula into the formula bar. WPS will automatically highlight the referenced columns in distinct colors.
4. Drag to fill: Press Enter, then double-click the small square at the bottom right of the active cell to automatically fill the formula down to the last row.

100% compatible with Microsoft Excel formulas and table references.Intuitive formula builder helps you troubleshoot syntax errors automatically.Lightweight and fast, even when managing thousands of rows of tracking data.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my nested IF formula return a #NAME? error?

A #NAME? error usually occurs if you are using structured table references (like [@[Start Date]]) but your dataset is not formatted as an official Table, or if there is a typo in the column header name within the formula. Try converting your data to a Table or use standard cell references like B2.

Can I add more status categories, like 'Delayed'?

Yes. You can expand the nested IF formula to include additional conditions. For example, you can use the TODAY() function to check if the current date exceeds an expected deadline before returning 'In Progress', thus adding a 'Delayed' status.

Does this IF formula work exactly the same in Excel and WPS Spreadsheet?

Absolutely. The IF function, logical operators, and structured table references are standard spreadsheet features that function identically across Microsoft Excel and WPS Spreadsheet without any modifications.

Excel & Data Problems