Flag overdue tasks
Flag a task when its due date is before today and its status is incomplete.
Copyable formulas
English Excel · en-US
=IF(B2="","",IF(AND(B2<TODAY(),C2<>"Complete"),"Overdue",""))Turkish Excel · tr-TR
=EĞER(B2="";"";EĞER(VE(B2<BUGÜN();C2<>"Tamamlandı");"Gecikti";""))Sample data
Enter the headers and sample records in A1:C5.
| Task | Due date | Status |
|---|---|---|
| Count | =TODAY()-1 | Open |
| Review | =TODAY() | Open |
| Report | =TODAY()-2 | Complete |
| Log | Open |
Fill down through D5. Enter statuses from a controlled list.
Enter the formula in D2.
To try the Turkish formula, use the sample data on the Turkish page.
Expected result
Overdue, blank, blank, blank
How it works
Tasks due today do not cross the cutoff. Completed tasks and blank due dates also remain unflagged. Format the due date cells as dates. For a fixed report date, replace TODAY() with a reference to a cell containing that date.
Compatibility
Excel for Microsoft 365 and Excel 2016, 2019, 2021, and 2024.