Flag Overdue Tasks Automatically

Excel & Google Sheets

How it works

AND checks two things at once: the due date in B2 is before today, and the status in C2 is not Complete. Only when both are true does IF return Overdue; otherwise it returns On Track. Completed tasks never show as overdue no matter how old their due date, and future-dated tasks stay On Track. The flag updates itself daily because TODAY() recalculates.

B2<TODAY()

Is the due date in the past?

C2<>"Complete"

Is the status anything other than Complete? <> means "not equal".

AND(…)

Both conditions must be true for the task to count as overdue.

"Overdue","On Track"

What to show when overdue, and what to show otherwise.

When to use it

Use this as the status engine in project trackers, client deliverable sheets, and invoice logs — then point conditional formatting or COUNTIF at the flag column.

Common mistakes

• ✕Blank due dates get flagged as overdue.

  ✓A blank date is treated as 0, which is always in the past. Guard it: =IF(B2="","",IF(AND(B2<TODAY(),C2<>"Complete"),"Overdue","On Track")).

• ✕Status values don't exactly match "Complete".

  ✓"Completed" or "complete " (trailing space) won't match. Standardize the status column with a dropdown.