The Problem
An overdue invoice is not just about the date. A bill can be past due and still not matter anymore if it has already been paid. That is why a useful invoice check needs both the due date and the payment status.
This challenge combines those two pieces into one flag. We label invoices as overdue only when they are unpaid and earlier than the reference date in G1, then count how many critical cases are in the list.
How We Solve It
The logic has two conditions that must both be true. The due date must be earlier than the current reference date, and the payment status must still say unpaid. AND handles that combined test, and IF turns it into the visible result.
Method 1: Basic late-date check
Method 1: Compare the due date to the current date.
If you only want to know whether a date is in the past, a direct comparison is enough. That is useful as a starting point, but it is not the full rule for this worksheet because paid invoices should stay out of the alert list.
This solves the date side of the problem by telling us whether the invoice is already late. It is a useful first check, but by itself it does not answer whether the invoice still needs action.
=IF(B2 < TODAY(), "OVERDUE", "OK")
Method 2: Add payment status with AND
Method 2: Only raise the alert when the bill is late and still unpaid.
This is the logic the validator is looking for. The row becomes OVERDUE only if the invoice due date is earlier than the date in G1 and the payment status is Unpaid. Any other combination returns OK.
This solves the full challenge because it combines both conditions that matter to the finance team. The formula checks whether the invoice is late and whether it is still unpaid, then turns that combined result into the final alert in column D.
=IF(AND(B2<$G$1,C2="Unpaid"),"OVERDUE","OK")
Method 3: Calculate days late
Method 3: Calculate how many days past due an invoice is.
After the main flag is working, the next useful step is often aging analysis. Subtracting the due date from the current date gives the number of days late, which can help prioritize the most urgent follow-ups.
This solves the follow-up prioritization problem. Once overdue invoices are identified, the day count helps show which late bills are only slightly overdue and which ones need more urgent attention.
=$G$1-B2
Function Explanation
1. AND
AND returns TRUE only when every condition is true. That is why it works well here: the invoice must be late and unpaid at the same time.
Learn more this functionAND
2. IF
IF converts the logic test into a status the team can read quickly. It turns the formula from a hidden check into a visible workflow label.
Learn more this functionIF
3. COUNTIF
COUNTIF counts how many rows ended up marked as OVERDUE. That gives a simple summary number for the tracking section.
This file uses the fixed date in G1 instead of the live TODAY() result so the challenge stays stable and the validator can check one consistent answer.
Flag each invoice as overdue only when it is unpaid and earlier than the date in G1, then finish the summary so the sheet shows the total number of records and how many overdue alerts need attention.