Quick answer: Before asking AI for a task-tracker formula, define what “overdue” means. Then test a past date, today, a completed task, a missing date, a text date, and a future date. A formula that works on one normal row can still mislabel the rest of your tracker.
You need a list of work that is late. An AI assistant suggests a date comparison, and Excel accepts it. But should a task due today be late? What happens when the date is blank? Does a completed task stay on the list? Those are business rules, not details the assistant should decide for you.
Updated October 30, 2026. This guide provides an editorial reference formula and fictional test cases, not a recorded response from a particular AI product. The six cases were calculated in a temporary workbook and independently recalculated in LibreOffice Calc. They were not run in Microsoft Excel; repeat the checks in your own Excel version before using the formula.
Who this is for—and when to skip AI
This method fits a small action list maintained by a coordinator, analyst, or team lead. Use an organization-approved text assistant to help translate your rule into a formula and explain its references. You can provide column names and invented rows without uploading the real tracker.
If you already understand the rule and formula, enter it directly. Choose a maintained task-management system when you need reminders, permissions, or an audit trail. A spreadsheet formula labels rows; it does not send notifications or establish who is responsible for following up.
1. Write the rule before the prompt
For this example, a task is overdue when its due date is earlier than the review date and its status is Open. A task due on the review date is not overdue. Done tasks are excluded. Missing or text dates must remain visible as data issues.
Use A for Task, B for Due date, C for Status, and D for Review flag. Keep status values controlled to Open or Done. Put the review date in H2. For a repeatable test, enter =DATE(2026,10,30) in H2 and display it as a date.
Use genuine spreadsheet dates in B. A cell’s date-like appearance does not prove it contains a date value. In particular, a pasted text string needs checking before you use it in date calculations.
2. Give the AI a specification it can follow
Copyable prompt: I am building an Excel task tracker. Column B is Due date, C is Status, and D is Review flag. Data starts in row 2. H2 contains a valid numeric review date. Status is restricted to Open or Done. Return one formula for D2 that I can fill down.
Rules: Done returns an empty string. For an Open task, a blank due date returns “Missing date”; a nonnumeric due date returns “Check date”; a numeric due date earlier than H2 returns “Overdue”; otherwise return an empty string. Keep H2 fixed when filling down. Do not silently hide spreadsheet errors.
Also ask: Explain each branch and give expected results for six cases: yesterday/Open, today/Open, yesterday/Done, blank/Open, text date/Open, and tomorrow/Open. State the assumptions and unsupported inputs. Do not use macros or modify my workbook.
Review the explanation against your specification. If the assistant changes “earlier than” to “on or before,” it has changed the rule even if the formula is syntactically valid.
3. Compare the suggestion with a reference formula
Under the stated assumptions, this reference formula belongs in D2. The line breaks are for readability; you can enter it as one line.
=IF(C2="Done","",
IF(B2="","Missing date",
IF(ISNUMBER(B2),
IF(B2<$H$2,"Overdue",""),
"Check date")))The first IF excludes Done tasks. The second catches missing dates. ISNUMBER separates numeric values from text, and the final IF compares the numeric due date with H2. Microsoft documents IF-based logical tests and ISNUMBER’s type check.
$H$2 stays fixed as the formula moves down. B2 and C2 become B3 and C3 on the next row. A formula that accidentally changes H2 to H3 can produce believable but inconsistent results.
4. Run the six-row check
Keep H2 fixed at October 30, 2026. Enter the real-date cases with DATE formulas so local date parsing does not alter the exercise. Leave the missing-date cell truly empty. For the text-date case, type an apostrophe before 2026-10-29; the apostrophe forces text and is not part of the displayed date.
| Due date / status | Expected flag |
|---|---|
| Oct 29 / Open | Overdue |
| Oct 30 / Open | Empty string |
| Oct 29 / Done | Empty string |
| Blank / Open | Missing date |
| Text “2026-10-29” / Open | Check date |
| Oct 31 / Open | Empty string |
Fill D2 down through the six rows. Compare every result with the expected column, including the empty strings. Then inspect a middle and final formula to confirm the row references changed and H2 did not.
All six results matched the table in the temporary workbook calculation and LibreOffice recalculation. This checks the stated example, not every possible Excel input or an AI model’s overall reliability.
5. Decide whether the review date should move
For a live tracker, you can replace H2 with =TODAY(). Microsoft describes TODAY as returning the current date’s serial number and advises checking automatic calculation when the date does not update as expected.
For a dated weekly report, retain a fixed review date. Otherwise reopening the workbook later may change which tasks appear overdue. Label H2 clearly so readers know the date against which the list was reviewed.
Do not use a changing current date to reproduce the six-row exercise. Its expected answers are tied to October 30, 2026.
Limits to fix before using a real tracker
ISNUMBER does not prove a number is a sensible deadline. Zero and an unrelated numeric ID are numeric too. Validate date inputs against the tracker’s agreed date range. This formula assumes date-only values, not time-of-day deadlines.
Statuses need validation. An empty status, a trailing space, Cancelled, or another unexpected value is not handled as a separate data issue here. Clean or validate the status column before applying this two-status rule, or extend the specification and tests.
Errors should stay visible. An error in a referenced input may propagate to the result. Microsoft’s IFERROR documentation explains how fallbacks work, but wrapping everything in an empty-string fallback could make broken data disappear from review.
A blank flag does not mean the task is complete. It can mean Done, due today, or due later. Keep the Status and Due date columns visible. If those states must be distinguished, request separate labels and test the expanded rule.
Formula separators and function names can vary with Excel language and regional settings. If the formula is rejected, check those settings and your version before changing its logic.
Use the same checks on the next formula
Ask AI to explain the rule, references, and edge cases before applying its suggestion to live rows. Keep a few representative test rows in a copy of the tracker whenever you change the rule.
If you are still extracting tasks, start with the meeting-notes action-list workflow. For moving a checked list into Excel, see the CSV import example.
Official sources and verification
Microsoft: logical tests with IF; IS functions; TODAY; IFERROR. Checked October 30, 2026. No AI subscription price, product-specific AI feature, or productivity gain is claimed. The formula example uses ordinary worksheet functions and a fictional task list.