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
| Customer | Paid invoices | Simple average | Weighted average |
|---|---|---|---|
| Customer A | 3 invoices of $2,000, $2,000 and $20,000, paid in 30, 30 and 75 days | 45 days | 68 days |
| Customer B | 12 invoices, all paid in 28 to 35 days | 31 days | 31 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.
- Accounts receivable aging summary (under Who owes you) as of today, for each customer’s balance.
- 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.
| Option | Good for | Watch for |
|---|---|---|
| Do it yourself in Excel | A one-off answer before a credit review | A monthly rebuild in a workbook one person understands |
| Buy a tool: QuickBooks Online Advanced or a reporting app connected to QuickBooks | Saved, scheduled reports | Check its days to pay against your Excel version |
| Hire your bookkeeper or a freelancer | A monthly report alongside the books | Whether the credit and prepayment rules are written down |
| Bring in an outside team (Reports & answers) | A report built once from your books and kept running | Agree 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
- Reports included in your QuickBooks Online subscription, Intuit QuickBooks,
- Unapplied cash payment income on your profit and loss, Intuit QuickBooks,
- Create and apply credit memos or delayed credits in QuickBooks Online, Intuit QuickBooks,
- Customize your reports with a modern view in QuickBooks, Intuit QuickBooks,
- Export reports to Excel from QuickBooks Online, Intuit QuickBooks,
- AVERAGEIFS function, Microsoft Support,
- Run an accounts receivable aging report in QuickBooks, Intuit QuickBooks,
