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.
Finance teams run into this all the time when a tracker mixes paid and unpaid rows. If the sheet only checks due dates, it can raise false alarms for invoices that are already settled. If it only checks payment status, it misses the urgency of an unpaid bill that is already late.
The flow below shows why both conditions need to meet before the alert should fire.
The Problem: Overdue Means Late and Still Unpaid The alert should depend on the due date and the payment status together.
In this workbook, every invoice has a due date, a payment status, and a fixed reference date in the sheet. The challenge is to flag only the invoices that are both unpaid and earlier than the reference date, then summarize the number of records and critical alarms.
- Paid invoices should not become alarms just because their due date is old.
- Unpaid invoices should only become overdue when the due date is before the reference date.
- The summary should count the final overdue labels.
That keeps the tracking sheet practical. It focuses attention on invoices that still need collection action, not rows that only look old.
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. Since the dates are stored as text in this workbook, the solution rebuilds real date values before comparing them.
=IF(AND(DATE(LEFT(B2,4),MID(B2,6,2),RIGHT(B2,2))
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.
This prevents noisy alerts. A paid invoice with an old due date and an unpaid invoice that is not late yet both stay out of the overdue count.
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.
That label is what makes the tracker useful for follow-up, because the finance team can filter or count overdue rows immediately.
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.