cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Level 2

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

 
18 Comments 18
Highlighted
QuickBooks Team

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.
Also, you can check if there is a third party reporting application that would create this report for you in the Appcenter.
 
I'll be around if you have any questions about QuickBooks. Have a good one!
Highlighted
Level 1

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

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.

Highlighted
QuickBooks Team

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

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!

Highlighted
Level 1

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

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.

Highlighted
QuickBooks Team

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

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.

Highlighted
Level 1

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

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

Highlighted
QuickBooks Team

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

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. 

Highlighted
Level 3

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

 

 

 

 

Highlighted
Level 3

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

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.

 

Report Average days to Pay.JPG

 

Hopefully, this will make the formulas easier to work will

I (eye)IF(C7="Payment",E7,I5)
JIF(I8=I7,J7,G8) 
KIF(C7="Invoice",J6-G7,-1)
LIF(I7=I6,"",AVERAGEIFS(K:K,I:I,I7,K:K, ">=0"))

 

And the bottom:

LAVERAGE(L7:L34)
LMINIFS(F:F,C:C,"Invoice")
LMAXIFS(F:F,C:C,"Invoice")

 

Hope that helps.

 

 

Highlighted
QuickBooks Team

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

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.

Highlighted
Level 1

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

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.   

Highlighted
Level 3

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

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)

 

snip.PNG

 

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.

 

 

 

 

 

 

 

Highlighted
Level 1

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

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!!!

Highlighted
QuickBooks Team

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

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. 
 

Highlighted
Level 1

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

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?

 

Highlighted
QuickBooks Team

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

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!

 

Highlighted
Level 1

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

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!

Highlighted
Level 1

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

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.

  

Need to get in touch?

Contact us