How do I create an average days to pay report for one of our clients?

Hi there, swoodrum.

Currently, the report to show the Average Days to Pay report is unavailable in QuickBooks Online. However, I can guide you to prepare a similar report as a workaround. 

You can send reports to excel to calculate. Note that you can only export a report to excel one at a time.

Here's how you ca do it:

1. From the left menu, select Reports.
2. Search for and open the report you'd like to view.
3. Select the Export icon, then choose Export to Excel.

What kind of workaround is this? This is the laziest response that you could have possible given. "We don't have a report to show you the average amount of days it takes for your customers to pay you. But, did you know that you can export a report to Excel?" How does that help? At least tell us which report to run and how to customize it so that we get the information we are looking for, since your dumpster fire of a product doesn't actually provide a streamlined method of managing customer's accounts.

Hello there, @mhwill.

I'm here to help guide you today so you'll be able to get or pull up the report/s you need.

If you're trying to run a report that shows your customers transactions with payments terms, then you may run the Transaction List by Customer report and make the necessary customization.

Let me show you how:

1. Click on Reports at the left pane.
2. Select Standard.
3. Look for the report on the Reports page, or search Transaction List by Customer on the search field.
4. Customize the date.
5. Click on the small Gear icon beside the Export icon.
6. Add the Due date on the reports column.
7. Click on Run Report to refresh the page.

If you wish to export the report, simply click on the Export icon and select on Export to Excel.

In addition, you may also check this tutorial to learn more about customizing reports in QuickBooks Online: Customize Reports and Email.

Feel free to add a comment below if you have any other questions about reports in QuickBooks, I'll be always here to help you. Wishing you the best!

You actually work for Quickbooks and you don't know even know what an Average Days to Pay Report is? How shameful.

We are looking for a report that tells us on average, how long does it take for each of our customers to pay their invoices in full. QBO currently has a field to payment terms that you can adjust per customer, but in their infinite wisdom, they did not provide a means to enforce those terms or even provide access to a report  so you can manually check to see if your customers are paying within their terms.

If I wanted a report that tells me how much each customer owes and when it would be due, which is what your above directions would provide, then I would just check the A/R aging summary.

Hi mhwill,

The Average Days to Pay report is only available in the desktop version. As a workaround in QuickBooks Online, you can open the Invoices and Received Payments report. Then, export it to Excel so you can create formulas to get the difference of the dates of the invoices and their payments. Then, get the average of the differences to get the Average Days to Pay per customer.

The workaround I suggested is ideal if you have a few transactions. However, if you have a lot of them, I'd suggest finding third-party apps that can extract the average days to pay per customer.

If there's anything that I can help you further, feel free to go back to this thread.

Unbelievable! Why is this report not available? It's been in QB desktop forever - this is so shoddy...

I know you'd like this report to be added, Paulwood.

I'll help you by sending your feedback to our management team. They will pass this along to our software engineers to be reviewed. Then, this might be included in future updates.

In the meantime, you can follow my colleague JessT's workaround.

Let us know if you have other concerns. We'd be here to help you out. 

Good evening All,

I'm not terribly surprised in QBO's deficiency -- the lack of this report is just another example, but here is my solution:

Three parts are shown: 1) the basic QBO Invoices and payments report (Col A-G), 2) the "Average" calculations (Col I-L), and 3) the formulas used in Col O.

A couple of "helper columns are used to "realign" the payment with the corresponding invoices. The formulas listed in col L, are each copied to the respective columns, then copy & pasted to the bottom of the report -- in my case row 34.

The first problem is in separating and identifying which lines are payments versus invoices.  The col I & J formulas check and transfer only the Payment number and date, but for each line/invoice to which they apply. Col K formula returns the number of days between the invoice date (Col B and payment date Col J), but only for invoices -- payments always return -1.  Finally (almost), Col L take the average of all payments with the same number (Col I),  but does not include the payment (K= -1) in the average -- this happens only when the payment numbers change [caused by the outer if()].  Finally, (for real now) the bottom of Col L  a standard "Average()" is used to obtain the average days to pay.  Not to be an over achiever (LOL) but since I was here I added Min()/Max() but only on "Invoices", too.

Part of the beauty of this report, is that it can be easily used as a template by running the base report, then copying the report to the left side of the template, there will be some minor clean-up needed on the right side (based on number of rows), but the formulas should only need to be copied/moved down.

Hopefully, this will make the formulas easier to work will

And the bottom:

Hope that helps.

I am delighted to have you here, @jbowman1611.

I appreciate the steps in generating an average day to pay report that you've shared. The steps will help a lot of customers who are also having the same concern.

Your QuickBooks Online subscription comes with the ability to track income and expenses, send invoices, run payroll and pay and file your taxes. I suggest checking out these articles for tips on how to unlock the software's full potential: QuickBooks Online Help Articles.

You can post anytime if you have questions or want to share your best practices using the product. Have a great rest of your day.

Hi jbowman,

That's a great approach.  Are you sure about the formula for "J"?  "IF(I8=I7,J7,G8)" creates a circular logic error for me.

I've been trying to troubleshoot but I can't figure out the logic/formula.   

Hi @gaugemf ,

If you copy the formula J8

IF(I8=I7,J7,G8)

to J7

IF(I8=I7,J7,G8)

and then preface J7 with an '=' -- you'll certainly get a circular reference (as it did when I checked).

You want to enter the formula in J8 exactly as:

=IF(I8=I7,J7,G8)

with the leading '=', then copy the contents of J8  down the length of the column.  you should end with something like this (but the length of the column)

Essentially, the logic is to compare the current row of COL I with the previous row of column I (that is "Is this (CURRENT) row's Payment the same as the previous (I-1)?") if so Use the SAME DATE (J-1), otherwise (I'm on a new payment) get the new date (G(same row)))

Apologies for the confusion, but hope that helps.

I find it absolutely ridiculous that any useful feature that was always available in the desk top version hasnt been carried over to online, this is the main reason i held off so long changing. They took 4 years to show credits on remittance advices, my sister who used to do huge payment runs would have to print them and hand write them on! Its so frustrating!!!

I know you'd like this report to be added, @kirstyhawkins250381.

I can pass along all your feedback to the appropriate channels so you'll be able to reach a point of resolution. As soon as an update is available, I'll be always around to tell you and everyone in this thread can get back to business.

Drop some more questions here in the Community. We'll be glad to help. 
 

Anyone who owns a business that offers terms to their clients requires a report showing the Average Days to Pay of a customer.  Business owners share this type of information and cannot make an educated decision on the credit worthiness of a customer without this type of report.

This type of report NEEDS TO BE ADDED TO YOUR SOFTWARE IMMEDIATELY!!!  Quickbooks Online needs to work for us the customer.  Otherwise, what's the point of having it?

I understand the importance of running this report for you and your business, @Weapon99.

I'll also take note of this and send the suggestion to our product engineers. This way, they'll be able to know our customers' needs and might consider the feature in the coming updates. At this time, you may follow the workarounds suggested above so you can generate the information your need.

As always, feel free to visit our blog to stay current with the latest QuickBooks news and updates.

You can always add a comment below if you have any other questions. I'm a few clicks away to help. Have a good day!

Adding my agreement that QBO needs to have the "Average Days to Pay Report".

QBO users end up paying well more over time in subscription fees than Desktop/Enterprise users, we should have access to the same features and reports. I only use QBO because my team is remote, so access to QBO is easier and more reliable than remotely hosting Desktop/Enterprise.

I would be happy to export a report into Excel and do some magic to figure the payment averages out myself, but honestly, there is not really any workaround listed that will solve the issue for me other than spending hours manually calculating it via the "Invoices and Received Payments" report. Even if the payment date could be added onto the "Invoice List" report, that would be a huge help.

What I am going to use is the "Invoice List" with the "Last Modified" date column added, and make a wild and inaccurate assumption that the invoice is last modified when the payment is posted. That is only true if you always post the payment on the day it is received and never touch the invoice after that for any reason, but it is the closest thing that I have without a lot of extra manual work.

Please add the report for us soon. It should be a top priority for new features.

Cheers!

This truly is ridiculous.  Who is making feature decisions at QB? Obviously no one who has any experience in actually running a business.  The excel workarounds are time-consuming, inefficient do-ate-my-homework solutions.  What if I have a client with multiple outstanding invoices who pays several at once?  How can I do the suggested calculations?  Seriously, this needs to be prioritized.  I cannot believe that such a basic function EXISTED and was then deleted in the transfer to the online platform.

And what is this garbage about searching for an app?  Which app?!  Can't you even make a suggestion?  Basically what you're saying is we should solve this issue ourselves by doing our own research to fins someone who understands accounting better than you do and then pay them extra for a fix....  not impressive.

I agree. As well this is a must need report. Hopefully it is added only to the "essentials " version of quick books. Please Hurry. Also had an option to put customers on hold. 

Thanks

Lisa

I have been requesting an Average Days to Pay report in QB online for years.  It would be so easy for some programmer to add this feature!  Why is it only available in desktop?

It's been over a year now since this topic came up, and still no AVERAGE DAYS TO PAY report. 

This isn't the only vital business decision-making feature that's still missing from QB Online.

When do the promises stop, Intuit, and actual action happens, because it is extremely disappointing and unprofessional for a company to hear the MANY complaints of its paying customers and then to simply ignore them for months and years at a time. 

Dear QBO team: All the other paying users here are absolutely correct.  This is another basic report that should be in QBO and not require an "add-on" or "workaround" to accomplish.  Every time I talk to a fellow business owner about QBO I tell them to stay on QB Desktop.  It is cheaper, more robust and a more complete product.  I have discovered the hard way that QBO is merely a bait and switch unless you are a simple service based business. 

If you are suggesting we have to find a third party ap to fill the deficiencies of QB online, could you provide some examples of a third party ap that would meet these needs. It seems to me perhaps QB has partnered with said third party aps, purposefully leaving these dearths to force users to pay for another program.Why would a report already built in the desktop version be so difficult to translate to your online platform. 

I have seen this post about lack of reporting going back years, with responses that QB takes feedback seriously and will add these requests for future consideration but to date I have not seen any improvement. Very disappointing. 

Totally agree Theresa! I used this report all the time in desktop. 

QBO Team,

Another dissatisfied customer here. I loved using the Average Days to Pay report in QB Desktop. This is insane. It's been two years since this thread started. Your team can create a report (one that you already have in Desktop) once so that all of your customers don't have to spend hours "working around" your lack of customer attention. 

Get this done NOW! It'll be easy for you...and it's what you do, so do it!