Build an Accounts Receivable Aging Report in Excel

Подписаться 60 тыс.

Просмотров 290 тыс.

50% 1

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.

Хобби

Опубликовано:

31 окт 2009

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 82

@FantasyTalk 3 года назад

Holy cow you are a lifesaver I would have failed my final project without this video

@DannyRocksExcels 11 лет назад

Thanks! I am glad that I could help you with my tutorial. Life is precious and too short. Now, that you know how to create this report, you can slow down and smell the roses! Danny Rocks The Company Rocks

@vendmaster68 8 лет назад

Thank you Danny. You made this much easier.

@lucidpeachy 8 лет назад

Thank you for this video. This video was very helpful to me in my Accounting Class

@PhoRealSang 12 лет назад

WOW, Thank You Danny. I had a project due for class and this guided me through it all the way.

@Steph-qo6jx 4 года назад

Very helpful and informative video! Thanks so much!!

@DannyRocksExcels 11 лет назад

Thank you for your very kind words. I enjoy teaching and I enjoy helping people to get the most out of Excel. Thanks for taking the time to add your comments. Danny Rocks The Company Rocks

@prash8910 11 лет назад

Dear Danny You are doing an awesome JOB teaching us excel, I my self being Finance Professional (advanced excel user) watch ur tutorials all the time incase I stuck with excel formula somewhere many thanks Prash

@DannyRocksExcels 12 лет назад

My pleasure. I am happy that I could help you with your class project. Sincerely, Danny Rocks The Company Rocks

@rambimlilian4653 5 лет назад

Great work Danny your video helped me alot

@papo50 3 года назад

Your video was very helpful, Thanks.

@msvasan1973 10 лет назад

Thank you danny it was great presentation

@judzm 14 лет назад

Very informative lessons on excel. You Rock Danny! :)

@PautangHero 6 месяцев назад

Watched this in 2024 and it is a big help for me. Thanks a lot for this very informative video.

@DannyRocksExcels 12 лет назад

Thank you for your high praise!. I am pleased that you enjoyed the video. Danny Rocks The Company Rocks

@mohammedroshan3926 2 года назад

Ideal description.. Thank you

@FrancisQuigley 14 лет назад

excellent tutorial, thanks.

@sanjido 14 лет назад

thanks very helpful for my work

@jancovski 10 лет назад

Thank you!

@DannyRocksExcels 14 лет назад

@sanjido Thank you. I am pleased that I was able to make it easier to use Excel in your work. Danny Rocks The Company Rocks

@Merc60201 11 лет назад

This tip helped me immensely at work today. This is exactly what I needed and will build upon what I have learned. Thank you, thank you, thank you for posting!!!

@tsnstt 4 года назад

You get a thumbs up from me just because of your voice 😂👍

@DannyRocksExcels 14 лет назад

@judzm Thank you for writing, I am pleased that you found the video informative. Danny Rocks The Company Rocks

@DannyRocksExcels 11 лет назад

Hello Yuri - Thank you for pointing this out - I will try to find the time to go back and review the video to check this out. I apologize for the delay in responding. I have been moving my home and office and somehow missed the original notification. Danny Rocks The Company Rocks

@ko123kz 11 лет назад

Thank you Danny. You saved my life otherwise supervisor would kill me!

@pavankumar.hanmandla 11 лет назад

Thanks Danny......,

@sincerelyjerry244 2 года назад

Thank you

@filzuy7436 8 лет назад

Hi Danny, Can u help me, I'm new to AR in Excel, I am confused why is it that in last invoices they are not yet due but then they belong to over 90 days? I am just confused in this area.. Thank you so much for having this video... Great help for me and to anyone who is new to this topic...

@DannyRocksExcels 13 лет назад

@TheSherridan1 My pleasure! Thank you for writing to tell me that I helped you with this video lesson. Danny Rocks The Company Rocks

@devkhokhar 12 лет назад

simply genius

@osamahlhm9074 6 лет назад

excellent

@DannyRocksExcels 13 лет назад

@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

@DannyRocksExcels 14 лет назад

@riverhousefoto Thank you - I am pleased that you found this lesson helpful. Danny Rocks

@DannyRocksExcels 12 лет назад

Sorry for the delay in responding - I just found your comment. Begin you formula with =IF( - then "nest" the AND() function inside. I am having difficulty in getting my response to post without an Error Message. Danny Rocks The Company Rocks

@DannyRocksExcels 11 лет назад

Hi R J - You have several questions here: 1) I would us =IF() statement to indicate "Paid" when the cell for Amount Received is = Amount Due. You could "nest" IF()s to indicate "Partial Payment'" etc. 2) You should have a Column for Payment Received Date. 3) The value 41230 is the Serial Number that indicates how the Date is stored. Simply change the formatting of that cell to a Date Format. Danny Rocks The Company Rocks

@parvinichristian7339 7 лет назад

Hi Mr.Danny can you please give an example for Inventory ageing schedule .

@andykumar7808 5 лет назад

Hi Danny, Thanks you so much to teach us Aging report in excel. I'm little bit confused with the column of over 90 days. All amounts which are not over 90 days, these are showing in over 90 days column. Can you please teach us how we can fix this problem. thanks

@DannyRocksExcels 11 лет назад

Hello Tim - I will need to go back and review my video and to check this out. As soon as I have the chance, I will do so and respond. Danny Rocks The Company Rocks

@harheeniuthayasooriyan4173 Год назад

HI, if let say the customer paid within 30 days do u have any formula to show that the payment completed so that the due days function column wont run?

@AdvocateOfJamaica 12 лет назад

Hi Danny. What were the formula(s) you used to arrive at the amounts in the "total outstanding" and "total past due" cells in the beginning of the video? Thank you for the post, this video was very helpful. Is this the updated version?

@NightSociety 8 лет назад

Newbie, how do you copy or formula for the copy as on 5:03 ?

@agustinamacias8533 4 года назад

HELLO - was the problem solved? I noticed that all invoices that are 90+ days past due AND all current invoices are thrown in the 90+ day category.

@DannyRocksExcels 13 лет назад

@mao3577 Thank you! What a great compliment. I appreciate you writing to tell me this. Danny Rocks The Company Rocks

@savannahquirk7171 9 лет назад

How could you do this using an HLOOKUP formula?

@DannyRocksExcels 11 лет назад

One option would be to use the WORKDAY() Function to include the Bank Holidays. There are 2 required arguments for WORKDAY() - The Start date (Invoice Date), and, the number of days worked (30 days in this case.) WORKDAY() automatically excludes Saturdays & Sundays. There is an optional 3rd argument - List of Holidays to be excluded. The result is a Serial Number to represent the future date. So, remember to change the formatting for this cell to a Date format of your choice. Danny Rocks

@itsmeronelynd2588 2 года назад

Hello, may i ask what is the formula for an updated payment by the customer? like for example customer A is paid every 13th of the month. What is the formula for aging for that?

@muhamedmamdouh334 3 года назад

wow, awesome....who's the hell dislike this video

@DannyRocksExcels 13 лет назад

@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

@PERFECT8808 12 лет назад

hey, great posting, however when i tried it and the amounts were "current" it went directly into over 90 days?! how can i fix it? thanks, Nina.

@itsmeronelynd2588 2 года назад

Anybody know how to make an aging report that calculate if the customer is already payment updated? What is the formula on that. Thank you

@shiju141 10 лет назад

Great

@stevenp9740 11 лет назад

Hi Danny, i've got a problem when i copy this formula down, =if($H$1-D2>0,$H$1-D2,"") Today() date is in cell H1 and Due date is column D, there's data in cell D2 but no data in cell D3, when i copy it down cell D3 shows a serial number, can i hide it untill i put data into cell D3 D4 D5 and so on?

@dareamos5077 Год назад

Hi Danny, Please I will like to enroll for advanced excel class. I would like to know the cost and whether certificate will be issued after completion. Thanks

@jaagofficial 6 лет назад

what is partial payment is past due ?

@bernardwidjaja352 3 года назад

Hi Danny, please teach me about an excel formula for stock aging

@1002vicky 12 лет назад

Hi I have a table of invoice Nos. where in i put all the details of quanities, invoice wise item wise. So in one single invoice there may be 3 or 4 items in which quantites differ.So I insert 3 rows with same invoice nos, But qualities and quanities differ. Now in another sheet, I want to lookup for this particular invoice No. and sum all the quantities to a particular field in the current sheet. How do i do that. Total confused.

@DannyRocksExcels 13 лет назад

@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

@babaweso 8 лет назад

great but what if the rows not only contains inovives , I mean if the rows contains a payments from the customer and you have to less the payment value same like to distribute the payments to the due invoices and only remaining the due invoices

@lennykush9332 8 лет назад

NICE QUESTION.... DID HE ANSWER THIS?

@priyankzala2930 7 лет назад

Wesam AlFataiery Hi.. In that case you ll need an extra column for payment received and another column showing only Outstanding payment. The outstanding column should be used in the formula instead of Amount coulmn as shown in the video

@javedhussain3661 8 лет назад

Thanks for the video but it will be more beneficial if you could paste the link to download the data for practice

@TheKa23 12 лет назад

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

@MikeMNE 4 года назад

Hello Danny. Your video is great, i was searching for something like this long time.But please, can you make formula if the bill have installments. For example, 100€ invoice date 01.01.2020, 5 months installments of 20€.Every first in the month he need to pay 20€, If he pay 15€ first month, then he will have 5€ for 1-31 day and so on.... I dont know did i explained it good, but if i am i would like you to help me please. Thanks in advance. 🥂

@matlibra 6 лет назад

thumbs up

@Colour.Purple Год назад

where to get the invoice register ?

@DannyRocksExcels 11 лет назад

I do not understand the terms DSO and ADD. Please give me more information so that I can determine if I can assist you. Danny Rocks The Company Rocks

@DannyRocksExcels 12 лет назад

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

@rjpab8 11 лет назад

Danny, How can I, once payment is received, change the # of Days Due (cell) to paid. (I would like to create another column for Payment Received Date) Also if I leave Due Date cell blank my # of Days Past Due cell reads 41230. Can you help with these issues? R.J.

@TheKa23 12 лет назад

@DannyRocksExcels ok thank you :)

@vttc50 11 лет назад

YES YURI, I DON'T GET THAT EITHER. What went wrong. and how do you get the total past due without doing it manually. Where is Danny on this?

@DannyRocksExcels 12 лет назад

There are at least four ways to solve your question: 1) The SUMIF() Function 2) The DSUM Function 3) Subtotals 4) a Pivot Table I have multiple RU-vid Videos for each of these four topics. Simply search my RU-vid Channel using these keywords to find the videos. Sincerely, Danny Rocks The Company Rocks

@Auktoriserad 12 лет назад

Copy the formula in "days past due"-cell and make and make an if statement to check the status cell if it's paid or un-paid. If it's paid then leave it " " otherwise it should execute the original formula IF(STATUS_CELL="Paid";" ";here goes original formula for days past due)

@YuriGural 11 лет назад

What I did is used the same formula =IF(AND($F6>90,$F6

@rickswineberg 3 года назад

1 suggestion, instead of the formula approach, run a pivot table and group the results

@YuriGural 11 лет назад

Thank you for your lesson. But is a little mistake in your last formula and OVER 90 DAYS. As you can see the Invoice #1005 is Past Due about 17-18 days. But you have this amount $7,319 in cell "Over 90 DAYS". And actually all the rest invoices fall into "Over 90 Days" even those which are not Past Due at all. What can you say about that?