Status Formula in Excel: Overdue, Due Soon or Done

A status formula in Excel turns a due date into a label like Done, Overdue, Due soon or On track, and updates itself every day. You build it with nested IF or IFS, comparing the due date with TODAY(). This guide builds one step by step for a task tracker, then handles the details that break real trackers: the order of the checks, blank dates, and a status that should not change overnight.

The status formula in Excel#

The formula checks each status in turn and returns the first one that fits. Here is a small task tracker. Assume today is 2 November 2026:

In D2, type:

=IF(C2="Yes","Done",IF(B2<TODAY(),"Overdue",IF(B2-TODAY()<=7,"Due soon","On track")))

Fill it down to D6 (Windows: Ctrl+D; Mac: ⌘D). Supplier review is due in 4 days, so it shows Due soon. Client report's date has passed, so it's Overdue. Draft budget is also past its date but marked Yes, so it shows Done.

TODAY() returns the current date and has no arguments. It recalculates whenever the workbook opens or changes, so the statuses move on by themselves. The TODAY function guide covers the details.

How the formula decides: order matters#

IF tests a condition and returns one value if it's TRUE and another if it's FALSE. A nested IF puts another IF in the FALSE slot, so the row moves to the next test only when the previous one failed. The IF function guide explains the three arguments.

The order is the logic. Put Done first, or finished tasks with past dates show as Overdue. Put Overdue before Due soon, because an overdue date also gives a negative number of days, which is less than 7. Step 3 only needs one test because step 2 has already removed the past dates.

Dates work in these tests because Excel stores each date as a whole number. B2-TODAY() is simply the number of days left: 4 for Supplier review. The guide on IF with dates goes deeper into date comparisons, and nested IF in Excel covers longer chains.

The same status formula with IFS#

IFS takes pairs of test and result and returns the result for the first TRUE test. It reads in a straight line, with no brackets piling up at the end:

=IFS(C2="Yes","Done",B2<TODAY(),"Overdue",B2-TODAY()<=7,"Due soon",TRUE,"On track")

The final TRUE,"On track" is the catch-all. TRUE is always true, so any row that fails every earlier test lands there. Without it, those rows return #N/A. IFS needs Excel 2019 or Microsoft 365; use nested IF if the file goes to older versions. The IFS function guide and the article on the IFS function show more examples.

How to handle blank due dates#

An empty cell counts as 0 in a date test, and 0 is earlier than any real date. So a task with no due date shows Overdue. Add a check at the very start:

=IF(B2="","No date",IF(C2="Yes","Done",IF(B2<TODAY(),"Overdue",IF(B2-TODAY()<=7,"Due soon","On track"))))

Now an empty date shows No date instead. If finished tasks may have no date, swap the first two checks so Done wins.

Add "Due today" and a days-left column#

To flag tasks due today separately, add one more test between Overdue and Due soon:

=IFS(C2="Yes","Done",B2<TODAY(),"Overdue",B2=TODAY(),"Due today",B2-TODAY()<=7,"Due soon",TRUE,"On track")

A days-left column next to the status helps people sort by urgency. In E2, type =B2-TODAY() and format the cell as General or Number, not as a date. Supplier review shows 4, and Client report shows -3 (three days late). For working days only, use NETWORKDAYS; the guide on counting days until a deadline shows both.

Freeze the status on a report date#

TODAY() changes every day, so last week's report shows different statuses when you open it again. When the status must stay fixed, for example in a monthly report or a job test, type the report date in a cell such as G1 and use it instead:

=IFS(C2="Yes","Done",B2<$G$1,"Overdue",B2-$G$1<=7,"Due soon",TRUE,"On track")

The $ signs keep the reference on G1 as you fill down.

Status from percent complete#

Not every tracker uses dates. If column E holds progress from 0% to 100%:

=IFS(E2=0,"Not started",E2<1,"In progress",TRUE,"Complete")

0% shows Not started, anything above 0% and below 100% shows In progress, and 100% shows Complete. You can combine both ideas: use E2=1 as the Done check at the start of the date formula.

Colour each status with conditional formatting#

A coloured status is easier to scan than text. Select A2:D6, then go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format and add one rule per status:

The $ before D keeps every cell in the row looking at the Status column, so the whole row changes colour. The guide on highlighting overdue dates shows how to colour dates directly, without a status column.

Practise it#

Status formulas combine three skills job tests love: IF logic, the order of conditions, and date maths. Practise nested IF and IFS in a live spreadsheet to build multi-outcome formulas, review the basics with the IF function lesson, and practise days between dates with TODAY. Each lesson checks your formula instantly. Then try the exercise on this page, which uses a fixed report date so the answer stays the same every day.