How do I set up a formula for an accounting aging in Excel that is date specific?

Answer 1

First off you need a current date or a specific date entered into a cell; e.g., "=now()" or "03/31/07" next you need to enter the invoices in the manner you would like to see them; e.g., Customer Name, Customer Number, Invoice Number, Invoice date, Invoice Amount; each entered into heading cells. Next you need to enter the breakdown you would like in the heading; usually, these are broken down as current, over 30 days, over 60 days, over 90 days, and total.

Next you need to compute the age of each column. =If(heading date-invoice date<=30,invoice amount,"") would go into the cell below current. =If(heading date-invoice date<=60,invoice amount,"") would go into the cell below over 30. and so forth. You can monkey around and use actual month ends if you'd like.

Next, because accountants love to foot and cross foot everything, you would likely enter the total under the total column, or =sum(previous three columns.) At the bottom of each customer's invoices you will enter in totals for each category. This can be done with an if statement for the customer number or you could sort the entire list by customer number and insert a column row. Finally, at the bottom you would enter totals for all customers and all invoices. I would probably tally the outstanding invoices as well with a count of the invoice numbers.

Answer 2

You should utilize an if/then formula. That is the way we did it in my finance classes. If you would like me to send you a sample spreadsheet, you can e-mail me at [e-mail]. Hope that helps!