ExcelFlux

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.

Microsoft Excel reference