The Date difference, aging bands guide
An aging report answers two questions per invoice: how many days past due is it, and which band does that put it in? Days overdue is the report date minus the due date, never below 0: =MAX(0, $H$1-B2). The band comes from a small table: =VLOOKUP(D2, $G$4:$H$8, 2, TRUE).
Days between two dates
Excel stores a date as a number of days, so one date minus another is the days between them: 3/31/2026 minus 3/1/2026 is 30. =DAYS($H$1, B2) gives the same answer; note the order, end date first. DATEDIF(B2, $H$1, "d") also works, but returns #NUM! when the start date is later than the end date, which happens for every invoice that isn't due yet.
Why a fixed as-of date, not TODAY()
TODAY() recalculates every time the file opens, so the report you sent on the 31st shows different numbers on the 2nd and nobody can reconcile it. Put the report date in a cell, label it "As of", and subtract from that cell, locked as $H$1. Change the one cell to rerun the report for another date.
Not due yet is 0, not negative
An invoice due after the report date gives a negative number. Wrap the subtraction so it never goes below 0:
=MAX(0, $H$1-B2)=IF($H$1>B2, $H$1-B2, 0) does the same thing in more words.
Aging bands
A common set is Current, 1–30, 31–60, 61–90 and over 90 days; use whatever bands your team reports. Two ways to assign them:
- A band table and VLOOKUP with TRUE. List the first day of each band in one column (0, 1, 31, 61, 91) and its name next to it.
=VLOOKUP(D2, $G$4:$H$8, 2, TRUE)takes the last "From day" that isn't more than the days overdue. The first column must be sorted smallest first. Changing a band means editing the table, not the formulas. - IFS.
=IFS(D2=0, "Current", D2<=30, "1-30", D2<=60, "31-60", D2<=90, "61-90", TRUE, "Over 90"). The conditions are checked in order, so each one only needs the upper edge.
The same logic is practised on test scores in the VLOOKUP approximate match challenge and the nested IF challenge.
The edges
30 days overdue belongs in 1–30, 31 days in 31–60. The test is <= 30, not < 30. An invoice due on the report date itself is 0 days overdue, so it is Current.
The summary
To finish the report, total each band: =SUMIF($E$2:$E$8, "1-30", $C$2:$C$8), or a pivot table with Band in Rows and Amount in Values. One trap when you type the band names into Excel: it can read 1-30 as a date. Type an apostrophe first ('1-30) or write the label as "1-30 days".
Common mistakes
- TODAY() in the report. The numbers change every day.
- Negative days. Invoices not yet due need MAX(0, …).
- Exact match in the band lookup.
FALSEonly finds 0, 1, 31, 61 and 91, so everything else is #N/A. - Working days instead of calendar days. Payment terms such as "net 30" count calendar days, so NETWORKDAYS gives a different age.
FAQ
- How do I calculate days overdue in Excel?
- Subtract the due date from the report date and stop it going below 0: =MAX(0,$H$1-B2), where H1 holds the as-of date. =DAYS($H$1,B2) gives the same difference.
- How do I create aging buckets in Excel?
- Make a small table with the first day of each band (0, 1, 31, 61, 91) and its name, then use =VLOOKUP(days,table,2,TRUE). Or use IFS with the upper edge of each band.
- Should an aging report use TODAY()?
- Not for a report you send. TODAY() changes every day, so a report sent yesterday no longer matches. Put the report date in a cell and subtract from that cell.
- Why does Excel turn 1-30 into a date?
- Excel tries to read anything that looks like a date as one, and 1-30 does. Type an apostrophe first ('1-30) to keep it as text, or use a label like "1-30 days".
This task is in the timed accounting practice test. What Excel interview tests check
