Spreadsheet Practice

How to group invoices into aging buckets in Excel

Formula (Excel and Google Sheets)

=LET(days,$G$1-B2,IFS(days<=0,"Current",days<=30,"1-30",days<=60,"31-60",days<=90,"61-90",TRUE,"Over 90"))

Work out the days past due once with LET, then pick the bucket with IFS: =LET(days,$G$1-B2,IFS(days<=0,"Current",days<=30,"1-30",…,TRUE,"Over 90")).

Result on the example
one result per row in D2, filled down to D8
Works in
Excel 2021, 2024 and Microsoft 365
Google Sheets
Same formula

See it work

The formula is already in D2, filled down to D8. Change any input and the result updates, or edit the formula to experiment.

How it works

LET calculates the days past due once and names it days. IFS returns the label for the first test that is true, so the tests run from the smallest bucket up: an invoice 31 days late fails the first two and lands in 31-60, and one exactly 90 days late is still 61-90.

=LET(days,$G$1-B2,IFS(days<=0,"Current",days<=30,"1-30",days<=60,"31-60",days<=90,"61-90",TRUE,"Over 90"))

Result"Current"

  1. days-15

    A name defined in LET, or a named range.

  2. $G$110/31/2026

    The value in G1.

  3. B211/15/2026

    The value in B2.

  4. $G$1-B2-15

    The - operator subtracts.

  5. days<=0TRUE

    The <= operator tests at most.

  6. days<=30TRUE

    The <= operator tests at most.

  7. days<=60TRUE

    The <= operator tests at most.

  8. days<=90TRUE

    The <= operator tests at most.

  9. IFS(days<=0,"Current",days<=30,"1-30",days<=60,"31-60",days<=90,"61-90",TRUE,"Over 90")"Current"

    IFS: Returns the value for the first true test.

    • test1days<=0TRUE
    • value1"Current"
    • test2, value2days<=30TRUE
    • test2"1-30"
    • value2days<=60TRUE
    • test3"31-60"
    • value3days<=90TRUE
    • test4"61-90"
    • value4TRUE
    • test5"Over 90"
  10. LET(days,$G$1-B2,IFS(days<=0,"Current",days<=30,"1-30",days<=60,"31-60",days<=90,"61-90",TRUE,"Over 90"))"Current"

    LET: Names values inside a formula.

    • name1days-15
    • value1$G$1-B2-15
    • name2, value2IFS(days<=0,"Current",days<=30,"1-30",days<=60,"31-60",days<=90,"61-90",TRUE,"Over 90")"Current"

In Google Sheets

The same formula works in Google Sheets: =LET(days,$G$1-B2,IFS(days<=0,"Current",days<=30,"1-30",days<=60,"31-60",days<=90,"61-90",TRUE,"Over 90")). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=XLOOKUP($G$1-B2,{0,1,31,61,91},{"Current","1-30","31-60","61-90","Over 90"},"Current",-1)Current, 1-30, 31-60…Looks the days up in a bucket table, where each number is the start of a bucket. Keep the table in cells to change the buckets easily.
=IF($G$1-B2<=0,"Current",IF($G$1-B2<=30,"1-30",IF($G$1-B2<=60,"31-60",IF($G$1-B2<=90,"61-90","Over 90"))))Current, 1-30, 31-60…Nested IF for versions without IFS and LET.

Common mistakes

  • Check the boundaries: exactly 30 days belongs in 1-30 here, because the test is <=30.
  • Labels like "31-60" sort as text in pivot tables. Prefix them ("1. Current", "2. 1-30") to keep them in order.
  • Paid invoices should drop out. If the payment date is in its own column, test it first: IF(paid<>"","Paid",…).

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Group invoices into aging buckets

In D2, put each invoice in an aging bucket (Current, 1-30, 31-60, 61-90, Over 90) by its days past due on the date in G1. Fill down to D8.

Questions

How do I total each bucket?

=SUMIFS(C2:C8,D2:D8,"31-60"), or a pivot table on the Aging column.

Should I age from the invoice date or the due date?

Both are common. From the due date, as here, shows days past due; from the invoice date shows the age of the invoice.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.