I receive an AR report showing statements billed and payments made by
customers for the last 12 months. It also shows the current balance.
I have been asked to create an aging report that determines if any of
the current balance is 30, 60 or 90 days overdue. Statements and
Payments are on separate rows and can vary widely. For example one
report I saw showed 5 months unpaid and when the customer finally did
pay they only part of one bill. Accounting always pays the older bill
first. I understand how to create the days overdue formulas but to
track unpaid amounts from over many rows is causing me a headache. I
can also write a VBA program to “look-back” several rows to determine
which bill this partial payment should be applied to but that started
to get way too complex. There must be a simpler way to determine
aging on the current balance. Any help will be appreciated.