Learning goals

Compute days overdue as the as-of date minus the due date
Explain why paid rows return an empty string instead of a number
Read the column as a triage list: 97 is a fire, -24 is patience

Concepts

As-of minus due

Days overdue is subtraction with a policy behind it: the AR as-of date minus the invoice's due date. Because both ends are single cells, the number is unambiguous and reproducible - anyone checking the work gets the same 97 for INV-1082 that you do.

The sign carries meaning. Zero or more: the invoice is late by that many days. Negative: the invoice is still inside its terms, and the number tells you how many days of grace remain. INV-1117 (due Oct 14, 2026) reads -14 at the Sep 30 as-of date - two weeks of patience left before it becomes a collections matter.

=IF($K5<=0,"",Settings!$C$7-$I5)

The IF guard first asks whether anything is even owed ($K5<=0 means settled). If not, return an empty string. Otherwise subtract the due date ($I5) from the as-of date (Settings!$C$7). For INV-1082: Sep 30, 2026 - Jun 25, 2026 = 97 days late.

Why paid rows go blank

The empty string is not cosmetic. Lesson 10 will slice this column with numeric criteria - SUMIFS conditions like ">60" - and in Excel a number compared with text simply does not match. So blank text can never fall into a bucket, and a settled invoice can never contribute to aging no matter how late it once was.

A zero would also be arithmetically harmless (its open balance is zero anyway), but it would lie to the eye: a paid invoice is not 'zero days late', it is not late at all. The empty string states that correctly and keeps the column scannable - four blanks in the current register, exactly the four Paid rows.

💡 Tips:

Do not 'tidy' the column by replacing blanks with 0 - you would undo the guard that keeps buckets honest.

The largest day counts and the largest open balances are rarely the same rows; read them together before deciding who to call first.

The column everything else reads

Days Overdue is deliberately the last computed column on the register because it is the first computed column of everything else. The aging buckets (Lesson 10) are numeric ranges over it. The credit-hold rule (Lesson 11) tests whether any invoice for a customer exceeds 60 on it. The dunning ladder (Lesson 12) picks its step from it.

At the September close the column reads: 97 and 70 (the two escalations), 48, 25, 10, 7 (the working overdue set), four blanks (paid), and -14, -18, -24 (patience). Ninety-seven days on $4,800.00 for Marigold Interiors is where any collections morning starts.

Practice

Open on Invoices. Cell M5 (Days Overdue of INV-1082) is blank - retype the guarded subtraction, then read the whole column as a triage list.

Type the days formula — Cell M5 is blank. Type =IF($K5<=0,"",Settings!$C$7-$I5) and press Enter. M5 returns 97: Sep 30, 2026 minus the Jun 25, 2026 due date.
Scan the overdue set — Read M5 through M17: 97, 70, 48, 25, 10, 7 for the six Overdue rows. Each number is how far past the promise the payment is - and each maps to a dunning step in Lesson 12.
Read the negatives — INV-1117 shows -14, INV-1118 shows -18, INV-1119 shows -24 (the Partial row - still inside terms). Negative days mean the grace period is still running.
Count the blanks — Exactly four rows show blank: INV-1105, INV-1110, INV-1113, INV-1115 - the four Paid rows. The guard, not a manual edit, put those blanks there.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

☐ Typed the guarded formula into M5 and got 97 days for INV-1082
☐ Verified the six Overdue rows read 97 / 70 / 48 / 25 / 10 / 7 and the not-yet-due rows read -14 / -18 / -24
☐ Confirmed exactly the four Paid rows are blank in column M
☐ Noted the baseline: days overdue drives the 0-30 / 31-60 / 61-90 / 90+ buckets that sum to $22,000.00 overdue

🧾 Lesson 09 · Days overdue on the register