Average days to pay report in QuickBooks Online, by customer

QuickBooks Online has no average days to pay report: Intuit’s list of reports in each plan has nothing with “days to pay” in the name. Build it from the Invoice and Received Payments report: export it to Excel, count the days from invoice date to paid date for each paid invoice, and average them by customer.

Field guide · Wholesale distributors · QuickBooks Online · Published · Get the PDF

Long exposure of a room of paper stacks at night: folds of light join one line that ends in a blue period

Which QuickBooks Online reports come closest?

These are all under Who owes you, and none averages payment time.

  • Invoice and Received Payments: invoices and the payments received on them. Build from this one.
  • Open Invoices: what is unpaid, plus payments not yet applied to an invoice.
  • Accounts receivable aging detail and Collections Report: what is owed and overdue today.

Aging shows who owes you now. Average days to pay shows how a customer pays over a year, which is what you set credit limits and terms on.

How do you build average days to pay by customer?

1. Apply loose payments first. Go to Reports, then Standard reports, and enter Open Invoices in Find report by name. Look for Payment in the Transaction Type column. To apply one, select the payment date, check the right invoice under Outstanding Transactions, and select Save and close.

2. Run the report. In Find report by name, open Invoice and Received Payments. Set a period that covers the invoices and their payments: for a year of invoices, run 15 months.

3. Export it. Select Export/Print, then Export to Excel. If data seems to be missing, select Enable Editing in Excel.

4. Make one row per paid invoice. Columns: customer (A), invoice number (B), invoice date (C), paid date (D), invoice amount (E). For an invoice paid in parts, use the date of the last payment.

5. Add days to pay. In F2, enter =D2-C2, fill down, and format as a number. For days past terms, subtract the due date instead.

6. Average by customer. List each customer once in column H. Then:

  • Simple average, in I2: =AVERAGEIFS(F:F,A:A,H2)
  • Weighted by invoice size, in J2: =SUMPRODUCT((A2:A2000=H2)*F2:F2000*E2:E2000)/SUMPRODUCT((A2:A2000=H2)*E2:E2000)

AVERAGEIFS returns #DIV/0! for a customer with no paid invoices in the range.

7. Check it. Open five invoices from different customers in QuickBooks. Invoice date, payment date and amount should match your rows.

Example

CustomerPaid invoicesSimple averageWeighted average
Customer A3 invoices of $2,000, $2,000 and $20,000, paid in 30, 30 and 75 days45 days68 days
Customer B12 invoices, all paid in 28 to 35 days31 days31 days

Customer A looks fine on the simple average. The weighted figure shows the large orders are the slow ones.

What throws the number off?

  • Credits that close invoices. With Automatically apply credits on, QuickBooks applies a credit to the oldest unpaid invoice, which then looks paid with no cash. The setting is under Settings, Account and settings, Advanced, Automation. Leave credit-closed invoices out.
  • Prepayments. A payment recorded before its invoice comes out at zero or negative days. Leave it out.
  • Short pays and write-offs. An invoice closed by a write-off was never fully paid. Count it separately.
  • Open invoices. Leaving them out flatters slow payers. Show each customer’s oldest open invoice next to the average.

Is there a faster estimate?

Yes: days sales outstanding (DSO) per customer, from two standard reports.

  1. Accounts receivable aging summary (under Who owes you) as of today, for each customer’s balance.
  2. Sales by Customer Summary for the last 90 days.

DSO = balance ÷ 90-day sales × 90.

Example: a customer owes $42,000 and bought $63,000 in the last 90 days. 42,000 ÷ 63,000 × 90 = 60 days.

It flags the customers worth the full build, but it can’t show which invoices were slow.

How do you stop rebuilding it every month?

Keep the workbook and paste each month’s export into the same columns. On QuickBooks Online Advanced, the Custom Report Builder can group by customer, average a numeric column with Edit group calculations, and email a saved report on a schedule. Build the Excel version first so you know the right answer.

OptionGood forWatch for
Do it yourself in ExcelA one-off answer before a credit reviewA monthly rebuild in a workbook one person understands
Buy a tool: QuickBooks Online Advanced or a reporting app connected to QuickBooksSaved, scheduled reportsCheck its days to pay against your Excel version
Hire your bookkeeper or a freelancerA monthly report alongside the booksWhether the credit and prepayment rules are written down
Bring in an outside team (Reports & answers)A report built once from your books and kept runningAgree in writing which invoices count before anything is built

To decide who to chase this week, the AR aging chase list tool turns the aging export into a call list. Slow payers also show up in gross profit by customer and in a borrowing base certificate, where old receivables drop out.

FAQ

Does QuickBooks Online have an average days to pay report? No. Build it from the Invoice and Received Payments export, as above.

Which date counts as paid for a partly paid invoice? The date of the payment that brought the balance to zero. Earlier partial payments don’t stop the clock.

Should I use a simple or a weighted average? Use both. The weighted one shows whether your largest orders are paid slowly.

Is average days to pay the same as DSO? No. Average days to pay measures paid invoices; DSO compares what a customer owes now with what they bought recently.

Can I get it without QuickBooks Online Advanced? Yes, with the Excel steps above. The Custom Report Builder, which can save and schedule it, is for Advanced and Intuit Enterprise Suite.

Sources

Have one problem in mind?

Walk us through it on a free 30-minute call. Within two business days you get a one-page note on what we’d fix first, yours to keep.

Walk us through one problem