How to Show At Risk, Open, or Closed Status in Excel
Question details
The user needs a formula to automatically display a task's status as 'At Risk', 'Open', or 'Closed' by evaluating the due date and resolution date.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking project or task statuses where items need to be flagged dynamically based on whether they are completed, ongoing, or currently overdue.
- Observed behavior
- The user wants a dynamic cell output that changes to 'Closed' when a resolution date is entered, 'At Risk' if the deadline has passed without resolution, and 'Open' if the deadline has not yet passed.
Ensure your worksheet has distinct columns for 'Due Date' and 'Resolution Date' formatted as valid dates, as the formula relies on accurate date values to calculate the status correctly.
Use Nested IF and ISBLANK Functions
This solution uses nested IF statements combined with the ISBLANK and TODAY functions to evaluate both the resolution date and due date dynamically.
The ISBLANK function first checks if the resolution date column is empty. If a date is present, the task is marked 'Closed'. If it is empty, a nested IF statement compares the due date to the TODAY() function to determine if the task is 'At Risk' (overdue) or 'Open' (still within the deadline).
Click on the cell in your worksheet where you want the automated status to be displayed.
Type the formula: =IF(ISBLANK(K2),IF(TODAY()>C2,"At Risk","Open"),"Closed") into the formula bar.
Replace 'K2' with the actual cell reference for your resolution date and 'C2' with the cell reference for your due date.
Press Enter to execute the formula, then click and drag the fill handle at the bottom-right corner of the cell down to apply the formula to your remaining tasks.

Track Project Statuses Easily with WPS Spreadsheet
You can seamlessly apply nested IF formulas, track project deadlines, and manage complex datasets using WPS Spreadsheet. It offers full compatibility with Excel formulas and provides a lightweight, user-friendly interface to streamline your daily project management tasks.
- 1. Open your tracker: Launch WPS Spreadsheet and open your project or task management workbook.
- 2. Input the formula: Select the status column and type the nested IF formula to evaluate your due and resolution dates.
- 3. Fill down: Press Enter and drag the fill handle to apply the automated status logic to all tasks in your dataset.

Frequently Asked Questions
Why is my status formula returning an error instead of the status?
Errors usually occur if the cell references in the formula do not match your actual data columns, or if your 'Due Date' and 'Resolution Date' cells contain text instead of valid date formats. Double-check your column letters and format the date columns properly.
Can I automatically highlight 'At Risk' statuses in red?
Yes. Select your status column, navigate to Home > Conditional Formatting > Highlight Cells Rules > Equal To. Type 'At Risk' in the dialog box and choose a red fill format to apply the styling.
How can I add an 'In Progress' status to this formula?
You will need to expand the nested IF statement. You can check an additional condition, such as whether a 'Start Date' cell is filled or a dropdown is set to 'Started', before the formula defaults to the 'Open' status.




