What You Will Learn
Five invoices. Each has a due date in B2:B6 and a payment status in C2:C6. The reference date (2026-04-05) is stored in G1. You need to flag rows where the due date has passed and the invoice is still unpaid.
Two conditions must both be true. AND handles the combined check, and IF turns it into an OVERDUE or OK label.
The formula for D2 (fill down to D6):
=IF(AND(B2<$G$1,C2="Unpaid"),"OVERDUE","OK")
Breaking it down:
- B2<$G$1 — is the due date before the reference date in G1?
- C2="Unpaid" — is the invoice still unpaid?
- AND(...) — only TRUE when both conditions pass.
- IF(... ,"OVERDUE","OK") — shows OVERDUE only for rows where both are true.
The dollar sign on $G$1 locks the reference date so every row compares against the same cell.
In this sheet, two invoices should be flagged as OVERDUE: Tesla Motors (due March 15, unpaid) and Ford Finance (due March 1, unpaid). Apple Tech has an old due date too, but it is already paid — so AND returns FALSE and the label stays OK. SpaceX and Nike have future dates, so they are not overdue regardless of payment status.
Two conditions must pass: date past due AND status unpaid.
The audit formulas:
- B9:
=COUNTA(A2:A6) — counts total invoices.
- B10:
=COUNTIF(D2:D6,"OVERDUE") — counts overdue alerts.
In this workbook the dates are real date values, so the comparison B2<$G$1 works directly. If your dates were stored as text, you would need to convert them with the DATE function first.
Going Further: Days Late
Once the flag is working, a helpful next step is calculating how many days past due each overdue invoice is:
=$G$1-B2
This subtracts the due date from the reference date and returns the number of days late. The result helps prioritize which overdue invoices need attention first.
A common mistake is checking only the date or only the payment status. Without AND, paid invoices with old dates would still show as overdue. Always ask: does this row need one condition or two?
Write an IF(AND(...)) formula in D2:D6 to flag unpaid invoices past the reference date. Use COUNTA in B9 for total invoices and COUNTIF in B10 for overdue count.