cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Experienced Member

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

 
12 Comments
QuickBooks Team

Hi there, swoodrum. Currently, the report to show the Ave...

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

Re: Hi there, swoodrum. Currently, the report to show the Ave...

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.

QuickBooks Team

Re: Hi there, swoodrum. Currently, the report to show the Ave...

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!

Experienced Member

Re: Hi there, swoodrum. Currently, the report to show the Ave...

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.

QuickBooks Team

Re: Hi there, swoodrum. Currently, the report to show the Ave...

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.

Active Member

Re: Hi there, swoodrum. Currently, the report to show the Ave...

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

QuickBooks Team

Re: Hi there, swoodrum. Currently, the report to show the Ave...

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. 

Senior Explorer ***

Re: Hi there, swoodrum. Currently, the report to show the Ave...

 

 

 

 

Senior Explorer ***

Re: 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.

 

 

QuickBooks Team

Re: 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.

Community Contributor **

Re: 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.   

Senior Explorer ***

Re: 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.