Date & Time
Invoice Age, Days Past Due & Collection Status Escalation Formula
Calculate open days past due against an as-of reporting date while short-circuiting settled invoices first, then classify each receivable into PAID, CURRENT, PAST_DUE_1_30, PAST_DUE_31_60, or ESCALATE_60_PLUS.
Why Paid_Date must short-circuit Due_Date comparison in AR status formulas
Subtracting Due_Date from As_Of_Date ($G$2 - B2) without checking Paid_Date first marks historically settled invoices from prior months as 60+ days delinquent! Conversely, open invoices whose Due_Date is still in the future yield negative differences that should clamp to 0 days past due rather than distorting average delinquency.
Checking NOT(ISBLANK(C2)) first immediately resolves settled rows to 0 days past due and status "PAID", while open rows compute MAX(0, $G$2 - B2) and step cleanly through your 1–30, 31–60, and 60+ collection dunning tiers.
| Invoice State | Days Past Due (D2) | Status Bucket (E2) | Action |
|---|---|---|---|
| Paid_Date populated | 0 | PAID | Closed — exclude from dunning |
| Open, As_Of <= Due_Date | 0 | CURRENT | Not yet due |
| Open, 1..30 days late | 1–30 | PAST_DUE_1_30 | Automated reminder |
| Open, > 60 days late | 61+ | ESCALATE_60_PLUS | Collections hold / account call |
Open Days Past Due Formula
Return 0 when Paid_Date (C2) is populated; otherwise clamp $G$2 - B2 at 0 with MAX.
=IF(NOT(ISBLANK(C2)), 0, MAX(0, $G$2 - B2))Collection Status Tier Classification Formula
Short-circuit settled invoices first, then evaluate D2 = 0, D2 <= 30, D2 <= 60, and 60+ escalation.
=IF(NOT(ISBLANK(C2)), "PAID", IF(D2 = 0, "CURRENT", IF(D2 <= 30, "PAST_DUE_1_30", IF(D2 <= 60, "PAST_DUE_31_60", "ESCALATE_60_PLUS"))))Try the example
Use the original values or change them to test your understanding.