Build an Accounts Receivable Aging Report in Excel

A viewer asked my help in building an A/R Aging Report. She wants to see the total for invoices that are "past due" 1 - 30 days, 31-60 days, etc.

In this video I use the =TODAY(), =IF(), =AND() and =WEEKDAY() functions to build this report.

If you have an Excel question, send it to me, I will answer it as soon as I possibly can do so.

My DVD-ROM, "The 50 Best Tips, Tricks & Techniques for Excel 2007" is now available for sale. Visit my website - www.thecompanyrocks.com/excels - for details on how to purchase it.

Category:

Howto & Style

Tags:

• Microsoft
• 2007
• Invoices
• Accounts
• Receivalbe
• Aging
• Past
• Due
• Functions
• Formulas
• Lessons
• Tutorials
• Learning

[TheKa23]

How do you use if() and AND() in the same cell?

[DannyRocksExcels]

@TheKa23

I am having trouble posting a response - not sure why. I will try to send you a message in response.

[MrAlexcode]

This is great, I have one question. I added a "status" & "paid" column. When costumer pays I put date in the "paid" column & status column goes to paid or left un-paid till they pay. (used if command to determine paid/un-paid status)

My question is, how to get the "days past due" column to zero once they pay, other wise it continues to count as over due

Days Past due =IF($B$2-E4, $B$2-E4, " ")

Staus Column =IF(F4,"Paid","Un-Paid")

Thank You

Alex Code

[DannyRocksExcels]

@MrAlexcode

Hi Alex -

I have many comments on this video, so I am going to produce an update to it. I am planning to do that this week and I will incorporate your question in the new video.

Sincerely,

Danny Rocks

The Company Rocks

[adebalayor]

on the first spreadsheet, what formula do you have in cell D19 ($80,886)? Is there a way to sum up all the past due automatically without having to select D6:D14?

Thanks

[DannyRocksExcels]

@adebalayor

In cell D19 I use the =SUMIF() Function which has 2 required arguments (Range and Criteria) and one optional argument ([sum_range]). The cells that you reference (D6:D14) are the cells for the "Range" that we need to "look in" to match the "Criteria" of = "Past Due."

So, the answer is "Yes" if you are going to use the "SUMIF Function." You could save yourself some time if you create a "named range" - e.g. "Status" for all of the cells in Column D.

Danny Rocks

[TheKa23]

@DannyRocksExcels ok thank you :)

[adebalayor]

@adebalayor Thanks Danny! This tutorial is really helpful!

[adebalayor]

@jhosen20091 I think so too

[DannyRocksExcels]

@jhosen20091

Good catch! I finally had a chance to go back and review this video and recreate the formulas.

I should have copied over - and editied the formula from cell I6 =IF(AND($E6>=91, $E6>120), $D6, " ")

I appreciate you letting me know this so that I can correct it.

Sincerely,

Danny Rocks

The Company Rocks