Skip to main content
C
Carl Endres
February 15, 2024
Question

How to calculate a customer's average days to pay over a specific period?

  • February 15, 2024
  • 2 replies
  • 0 views

I'm trying to track customer payment behavior over two different time periods, and I'd like to calculate the average time to pay from one period (Sep 2021 through Mar 2023) and compare it to another (Apr 2023 to present).  However, when I use the average time to pay report in Quickbooks, all that comes through is the total average time to pay for the customer, regardless of what time frame I band it to.  Is there a way to view a customer's historical average time to pay, or will the report only ever pull their present average?

2 replies

JoesemM
JoesemM
February 15, 2024

Hello there, @Carl11_2. Let me share insights about the average days to pay report in QuickBooks.

 

The Average Days to Pay report will help you determine how long your customers normally take to pay their invoices. It is available in both summary and detail formats. The detailed version shows the time to pay each invoice plus an overall average number of days.

 

In your case, you'll want to make sure that you filter it to the correct days so it will show in the report. To filter your report, you can follow the steps below:

 

  1. Click on Reports from the menu bar.
  2. Choose Customers & Receivables, then select Average Days to Pay.
  3. On that report, click Customize Report at the upper left.
  4. Go to The Filters tab, then on the Choose Filter field, pick Paid Through on the Filter drop-down.
  5. Select a date range on the drop-down list or on From and To, then OK.
  6. Filter the necessary details you want to include in the report.

 

On the other hand, you can also pull up the A/R Aging Detail report. It will display complete details on the average days your customers were able to pay their outstanding balance for a specific date range.

 

Here's how: 

 

  1. Go to the Reports menu.
  2. Select Customers and Receivables.
  3. Click A/R aging Detail.
  4. Choose Custom in the Dates drop-down arrow and filter the dates.

 

For additional reference, you can check this article on how to customize reports in QuickBooks Desktop.

 

In case you're working on the same reports on a daily, weekly, monthly, or quarterly basis, you can automate this task. For more details, please see this article: Set up and modify Scheduled Reports.

 

I'll be glad to help you again if you have more questions about running reports. I'm still here to help you. Have a good one!

C
Carl EndresAuthor
February 15, 2024

Ok, that helps, but is there a way to get the same results with the summary report instead of having to use the detail report?  The detail report outputs too much extraneous data to meaningfully manipulate when I just want Customer ID and given average for a date range.  I don't need the individual invoice listings that the detail report gives.

J
JanbonN
February 15, 2024

I appreciate your prompt response and thank you for providing additional information, Carl. Allow me to chime in on this thread and provide additional information.

 

The Average Days to Pay report displays the data of the current total average time your customers pay you. I understand that you only need the customer ID and average days.

 

Since this is a detailed report, we're unable to remove the invoices. You may consider unchecking the columns that you don't need for a simpler report.

 

Let's customize your report further:

 

  1. Run the Average Days to Pay report.
  2. Click Customize Report.
  3. In the Display tab, uncheck the details you don't want to show in the report.
  4. In the Filter tab, type in Paid Through.
  5. Choose Custom in the dropdown and fill in the From and To.
  6. Click OK.

 

Additionally, please check these articles to personalize your reports:

 

Return to this thread if you need further assistance with running your reports. I'll be here to help you in any way I can. Have a great day!

E
Erika_K
June 27, 2024

I have details on the availability of the Average Days to Pay report, PBJ10. I can also show you a workaround to obtain a copy of this financial data.

 

The Average Days to Pay report is unavailable in QuickBooks Online (QBO). As a workaround, you can pull up either the Transaction List by Customer or the Invoices and Received Payments or any report you can use to determine how long your customers generally take to pay their invoices. After that, export it into an Excel file to allow for the creation of formulas to calculate the variance between the invoice dates and their corresponding payments. Then, you can calculate the mean of these variances to find the average number of days it takes for each customer to make payments. 

 

Let me show you how:

 

  1. Go to the Reports menu.
  2. On the Find report by name, search for the suggested or any report.
  3. Customize the date and add the necessary filter.
  4. Once done, click the Export/Print dropdown menu and select Export to Excel.

 

Additionally, you can also explore other third-party applications to help you generate this type of report. You can directly find one on the QuickBooks App Store or through the QBO Apps menu.


Drop by again if you have more questions or follow-up concerns about the different financial reports in QBO or other related tasks, PBJ10, and I'll be sure to offer a helping hand. Just leave a comment below.  Stay safe, and don't hesitate to seek our assistance whenever necessary.   

P
PBJ10
June 27, 2024

If this feature is available in Quickbooks desktop and has been requested on QBO for years, it really needs to be implemented ASAP.

Since this is a known missing feature that is absolutely essential for basic bookkeeping and accounting, the steps above need more detail.

"export it into an Excel file to allow for the creation of formulas to calculate the variance between the invoice dates and their corresponding payments. Then, you can calculate the mean of these variances to find the average number of days it takes for each customer to make payments. "

How? What formulas will work? The file export does not show which payment paid which invoice or the payment date, so it's not obvious how to calculate anything between the invoice and its payment.

 

If this isn't possible, which third-party apps would work? Or how does our business migrate back to Quickbooks Desktop? 

    AlverMarkT
    AlverMarkT
    June 28, 2024

    I hear you, @PBJ10. I appreciate your input between QuickBooks Desktop (QBDT) and QuickBooks Online (QBO) reporting features.

     

    While I can't provide specific formulas and a particular name for the third-party app, I can still share guidance to accomplish your goal of creating the Average Days to Pay Report as in QBDT.

     

    It's correct that manually calculating the average number of days each customer takes to pay their invoices should involve data on the payment date and the corresponding invoice. The closest report we can generate in QBO is the Invoices and Received Payments report. Then, customize the report to show the invoice due date. Here's how:

     

    1. Go to Reports. Then, type in "Invoices and Received Payments," and select the report.
    2. Choose the Report period. Then, click on Customize.
    3. Select Change columns.
    4. Place a checkmark for the Due date column.
    5. Hit Run report.
    6. Click on the Export icon.

     

    In your Excel, you can consider organizing your data to show the Invoice Due Date, Payment Date, and Customer. Then, another column to calculate the difference between Payment Date and Invoice Due Date.

     

    Since this involves manual calculation, let me suggest keywords to help find a third-party app in your QBO. Here's how:

     

    1. Go to Apps in your QBO account.
    2. Click on Find apps.
    3. In the search bar, type keywords like "Advanced Reporting" or "Financial Reporting.
    4. Check the Overview or Review tabs for a summary of what the app offers.

     

    Also, if you're considering switching from QBO back to QBDT, please refer to this article: Move data from QuickBooks Online to QuickBooks Desktop.

     

    It's important that the software accommodates various business needs, and your experience highlights where it needs to improve. I still encourage sending feedback to our product engineers to consider adding this report in future updates.

     

    1. Go to the Gear icon.
    2. Select Feedback.
    3. Enter your comments or product suggestions.

     

    To get guidance on scheduling your sales reports, let me add this article as a future reference: Set schedule and email information for a memorized report.

     

    We'll be here in the Community if you have further questions about generating sales reports in QBO. We'll do our best to assist. Take care.

    Terms & ConditionsAccessibility statement