Excel Challenge 9: Identify Overdue Invoices

Challenge Scenario

In accounts receivable and corporate cash flow management, finance teams track invoice due dates against today's audit date to identify overdue customer invoices and calculate aging days for debt collection.

Calculating the difference between the audit date and the invoice due date allows financial controllers to instantly separate current bills from delinquent receivables.

As an Accounts Receivable Auditor, you are reviewing 4 invoices against the audit date stored in cell $G$2 (2026-09-15):

Days Past Due (Column D): Subtract Due Date from Audit Date (=$G$2 - C2). Positive numbers indicate the bill is overdue.
Collection Status (Column E): If Days Past Due is greater than 0 (D2 > 0), assign "OVERDUE"; otherwise assign "CURRENT".
Total Invoices Audited (Cell B8): Count total invoice records using COUNTA(A2:A5).
Total Overdue Invoices (Cell C8): Count how many invoices are labeled "OVERDUE" using COUNTIF.

Your Sheet Layout

The accounts receivable ledger is structured into clear operational sections:

Invoice Register (Columns A to C): Lists Invoice Number, Client Account Name, and Invoice Due Date.
Aging & Status (Columns D & E): Computes days elapsed past the due date and flags delinquent accounts.
Audit Date Parameter (Columns F & G): Contains the reference audit date in cell $G$2 (2026-09-15).
Receivables Summary (Rows 7 & 8): Tally of total ledger invoices and count of overdue accounts.

Cell Range	Column Name	Section	Formula / Approach	What It Does
`A2:C5`	Invoice ID, Client, Due Date	Invoice Register	Invoice metadata and payment deadlines	Read-only client invoice records.
`G2`	Audit Date	Reference Parameter	`2026-09-15`	Master cutoff date. Lock with dollar signs (`$G$2`).
`D2:D5`	Days Past Due	Aging Calculation	`=$G$2 - C2`	Calculates elapsed days past invoice deadline.
`E2:E5`	Status	Classification	`=IF(D2 > 0, "OVERDUE", "CURRENT")`	Flags invoices where days past due > 0.
`B8`	Total Invoices	Summary	`=COUNTA(A2:A5)`	Counts total invoices in the ledger (4).
`C8`	Overdue Invoices	Summary	`=COUNTIF(E2:E5, "OVERDUE")`	Counts total overdue unpaid invoices (2).

Challenge Objectives

Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.

Objective #1

Cell: D2-D5

In Column D (D2:D5), output "OVERDUE" if Due Date < $G$1 AND Status is "Unpaid", otherwise "OK".

Objective #2

Cell: B8

In cell B8, count total invoices using COUNTA(A2:A5).

Objective #3

Cell: C8

In cell C8, count "OVERDUE" invoices using COUNTIF(D2:D5, "OVERDUE").

Objective #4

Cell: D8

In cell D8, count "OK" invoices using COUNTIF(D2:D5, "OK").

Solution & Formula Breakdown

Let's examine how date subtraction and threshold testing calculate invoice aging:

1. Calculating Days Past Due with Absolute Referencing (D2:D5)

=$G$2 - C2

Subtracting the due date in Column C from the fixed audit date in $G$2:

Row 2 (INV-101, Due 2026-09-01): 2026-09-15 - 2026-09-01 = 14 days overdue → 14.
Row 3 (INV-102, Due 2026-09-20): 2026-09-15 - 2026-09-20 = -5 days remaining → -5.
Row 4 (INV-103, Due 2026-08-15): 2026-09-15 - 2026-08-15 = 31 days overdue → 31.
Row 5 (INV-104, Due 2026-09-30): 2026-09-15 - 2026-09-30 = -15 days remaining → -15.

2. Flagging Status with IF (E2:E5)

=IF(D2 > 0, "OVERDUE", "CURRENT")

If Days Past Due is positive (> 0), the bill is past its due date and labeled "OVERDUE"; otherwise it is labeled "CURRENT".

3. Audit Summary Metrics (B8 & C8)

Total Invoices (Cell B8): =COUNTA(A2:A5) returns 4.
Overdue Invoices (Cell C8): =COUNTIF(E2:E5, "OVERDUE") returns 2.

Remember to lock the audit date cell $G$2 with dollar signs. If left unlocked (=G2 - C2), copying down will shift the reference to empty cells G3, G4, and G5!