Formula Errors

How to Create an Excel Formula for Overdue and Completed Statuses

John Wilson Oct 1, 2026 868 views

Question details

The user needs an Excel formula to automatically display 'Completed' or 'Overdue' in a status cell based on comparing a task due date with a specific reference date and a completion indicator.

How to Create an Excel Formula for Overdue and Completed Task Statuses

Product: Excel
Device & OS: not provided
Scenario: Tracking task completion statuses and deadlines dynamically in a spreadsheet.
Observed behavior: The target cell correctly displays 'Completed' when criteria are met, 'Overdue' if the deadline has passed without completion, or remains blank otherwise.

Before you start

Ensure your spreadsheet has dedicated columns for task due dates, a completion indicator (like 'Yes' or 'No'), and a fixed reference date cell (such as today's date) to compare against.

Solution 1Recommended

Use Nested IF and AND Functions for Status Tracking

Combine the IF and AND functions to evaluate multiple conditions and return 'Completed', 'Overdue', or leave the cell blank depending on the task's current state.

By nesting IF statements and using the AND function, Excel can check multiple criteria at once. The first part of the formula checks if the task is complete and on time, while the second part checks if the deadline has passed without a completion mark.

Select the target status cell

Click on the cell where you want the task status to appear. Based on the scenario, this would be cell D9.

Enter the nested formula

Type the formula =IF(AND(C9="YES",B9>$F$2),"Completed",IF(AND(B9<$F$2,C9<>"Yes"),"Overdue","")) into the formula bar.

Apply absolute referencing

Ensure the reference date cell ($F$2) has dollar signs. This creates an absolute reference, preventing the cell target from shifting when you copy the formula to other rows.

Copy the formula down

Press Enter to apply the formula, then drag the fill handle (the small square at the bottom-right corner of cell D9) down to apply the status tracking to the rest of your task list.

Use Nested IF and AND Functions for Status Tracking

Understanding the formula logic: The formula first checks if C9 is 'YES' and the due date (B9) is after the reference date (F2). If not, it checks if the due date has passed and C9 is not 'Yes'. If neither condition is met, it returns a blank string ("").

Manage Tasks Efficiently

Track Task Statuses Automatically in WPS Spreadsheet

Easily apply logical formulas like IF and AND to manage your project deadlines using WPS Spreadsheet. It provides a seamless experience for building complex nested functions and managing project trackers.

1. Open your task tracker: Launch WPS Office and open your spreadsheet document containing the task list.
2. Input your data correctly: Ensure your due dates, completion markers, and reference dates are properly filled out in their respective columns.
3. Insert the nested IF formula: Click on the target status cell, paste the nested =IF() formula provided in the solution, and press Enter.
4. Drag to fill: Use the fill handle in the corner of the cell to copy the status formula to all other tasks in your project tracker.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx)User-friendly interface for building and troubleshooting complex nested functionsFree and lightweight alternative for data analysis and task trackingBuilt-in formula syntax checking and error highlighting

microsoft office alternative - wps office

Frequently Asked Questions

Why is my IF formula returning an error instead of the status?

This typically happens due to syntax errors such as missing parentheses or quotation marks around text values like "Completed". Ensure your commas and brackets perfectly match the required formula structure.

How can I automatically highlight the Overdue cells in red?

You can use Conditional Formatting. Select your status column, go to the Home tab, click 'Conditional Formatting' > 'Highlight Cells Rules' > 'Equal To', type 'Overdue', and choose a red fill format.

What is the purpose of the dollar signs ($) in the cell reference $F$2?

The dollar signs create an absolute cell reference. This locks the reference date cell (F2) so that when you drag and copy the formula down to evaluate other rows, the formula continues to compare against that exact same reference date without shifting down.

Excel & Data Problems