Lesson 6: Accounts receivable aging with dates and nested IF

Practice exercises for accountants, checked instantly in your browser.

Excel stores dates as serial numbers, so subtracting two dates gives a number of days. With the report date in H1, =$H$1-C2 is how many days the invoice in row 2 is past due. A negative result means it is not due yet.

Aging buckets come from a nested IF that tests from the smallest band up: zero or less is Current, up to 30 is 1-30, up to 60 is 31-60, and anything else is Over 60. Order matters, because IF stops at the first true test. Then SUMIF on the bucket column gives the total for each band.

Worked example: =IF(E2<=0,"Current","Past due")
A two-way split. The exercise adds more bands by putting another IF in the "false" slot.

Exercises

Exercise 1

Report date is in H1. In E2, calculate days past due (report date minus due date) and fill to E8.

Exercise 2

In F2, return the bucket: Current (0 or less), 1-30, 31-60 or Over 60. Fill to F8.

Exercise 3

Buckets are in column F. In H3, total the amount in the Over 60 bucket.