QuickBooks AR Aging in Google Sheets: How to Get the 30-60-90-Day Format QBO Can't Make

QuickBooks Online generates an AR aging report. It just doesn't generate it in the format anyone actually uses. If you've ever tried to hand a QBO aging report to a collections team, a CFO, or a lender, you already know the problem: QBO's output is a vertical list sorted by customer. The industry standard (and what every collections workflow is built around) is a columnar table with aging buckets across the top: Current, 1-30, 31-60, 61-90, 90+.

This guide shows you exactly how to get there, whether you're doing it manually with formulas or automating the whole thing.

Why QBO's AR Aging Report Doesn't Work for Most Teams

QuickBooks Online's built-in AR Aging Summary report gives you a list of customers with their total outstanding balances. The Aging Detail report gives you individual invoices. Both are structured as vertical lists: rows of customers or invoices, columns for aging buckets.

On the surface, that sounds like what you want. In practice, there are several ways it falls short.

The layout isn't formula-friendly. QBO's exported aging report includes header rows, subtotals, and grand totals mixed into the data rows. When you import this into Google Sheets, your formulas can't cleanly reference the data without first stripping out the non-data rows manually.

It's a snapshot, not a live view. The aging buckets in QBO's report are calculated as of the report date. If you need to track aging over time (to compare this month's 90+ bucket to last month's, for example) you're re-running and re-exporting the report repeatedly.

It doesn't pivot the way collections workflows expect. A collections team working a call list needs to see: customer name, total owed, broken out by how overdue each invoice is. They need to sort and filter by the 61-90 column, or flag everything in the 90+ column as priority. QBO's export gives you the raw ingredients but not the pivot.

It doesn't integrate with your other data. Collections teams often need to cross-reference AR aging against customer contact info, credit limits, or payment history: data that lives in a CRM or a separate spreadsheet. Having QBO's aging data in Google Sheets makes that join possible.

What the Standard 30-60-90 Columnar Format Looks Like

The format that collections teams, CFOs, auditors, and lenders all recognize looks like this:

Each row is a customer. Each column is an aging bucket. The amounts in each bucket represent outstanding invoice balances whose due dates fall within that range: measured from today's date, not the invoice date.

This format lets you:

• Sort by 90+ to prioritize collections calls
• Filter to customers with nothing current (a sign of a troubled relationship)
• Sum each column to see total exposure by aging bucket
• Track bucket totals over time as a trend metric

None of that is possible directly from QBO's export without building this structure yourself.

Step 1: Getting Your QBO AR Data into Google Sheets

You need two things from QBO: the invoice detail (customer, invoice number, invoice date, due date, amount, amount paid) and today's date (for calculating days overdue). The "amount due" is the amount minus any partial payments.

Option A: Manual CSV Export

In QBO, go to Reports > Accounts Receivable Aging Detail. Set the aging method to "Report Date" and set the date to today. Export as CSV. Import into Google Sheets.

Before you can use this data, you'll need to clean it:

1. Delete the header rows above the column labels (the report title, date, and basis rows)
2. Delete any subtotal and grand total rows
3. Convert currency columns from text to numbers (use Find & Replace to remove "$" and "," characters, then format the column as Number)
4. Convert date columns to actual date values if they imported as text

After cleanup, your raw data sheet should have one row per invoice with clean column headers: Customer, Invoice #, Invoice Date, Due Date, Original Amount, Amount Due.

Option B: Use a Direct Connection Tool

Skip the cleanup entirely by using a tool that connects directly to QBO's API and outputs clean, flat data. brooked.io, G-Accon, and Coupler.io all do this. The output lands in your sheet already formatted as clean columns with proper date and number types.

For the formulas in the next section to work correctly, your data needs to be in a clean flat table. If you're doing this manually, the cleanup in Option A is not optional.

Step 2: Building the Aging Buckets with Formulas

Assume your raw invoice data is in a sheet called Raw_AR, with the following columns:

• Column A: Customer Name
• Column B: Invoice Number
• Column C: Invoice Date
• Column D: Due Date
• Column E: Original Amount
• Column F: Amount Due (what's still owed)

The aging bucket for each invoice is determined by how many days have passed since the due date, calculated against today's date.

Calculating Days Overdue

In Column G (label it "Days Overdue"), add this formula in G2 and copy it down:

=IF(F2=0, "", MAX(0, TODAY() - D2))

This returns 0 if the invoice is fully paid (Amount Due = 0), and the number of days past due otherwise. MAX(0, ...) ensures invoices that aren't yet due return 0 rather than a negative number.

Assigning Each Invoice to a Bucket

In Column H (label it "Aging Bucket"), add this formula in H2 and copy it down:

=IF(F2=0, "Paid",
  IF(G2=0, "Current",
    IF(G2<=30, "1-30",
      IF(G2<=60, "31-60",
        IF(G2<=90, "61-90",
          "90+")))))

This assigns each invoice to exactly one bucket based on the days overdue. You now have a flat table where every invoice has a labeled bucket.

Alternative: Using DATEDIF for Days Overdue

If you prefer DATEDIF for readability:

=IF(F2=0, 0, IF(D2>=TODAY(), 0, DATEDIF(D2, TODAY(), "D")))

DATEDIF(start_date, end_date, "D") returns the number of complete days between two dates. The nested IF handles invoices that aren't yet due (due date is in the future) and fully paid invoices.

Step 3: Building the Columnar Summary Table

Now that every invoice has a bucket label, you can build the columnar summary table that collections teams actually use.

Create a new sheet called AR_Aging_Summary. Set it up with customer names in Column A and bucket columns across the top.

Setting Up the Summary Structure

In A1, type "Customer". In B1 through G1, type: "Total AR", "Current", "1-30 Days", "31-60 Days", "61-90 Days", "90+ Days".

To get a unique list of customers in Column A, use:

=SORT(UNIQUE(FILTER(Raw_AR!A2:A, Raw_AR!F2:F<>0)))

This pulls all customers with at least one open invoice, de-duplicates, and sorts alphabetically.

SUMIFS Formulas for Each Bucket

In B2 (Total AR for the first customer), enter:

=SUMIFS(Raw_AR!F:F, Raw_AR!A:A, A2)

In C2 (Current bucket for the first customer), enter:

=SUMIFS(Raw_AR!F:F, Raw_AR!A:A, A2, Raw_AR!H:H, "Current")

In D2 (1-30 Days bucket), enter:

=SUMIFS(Raw_AR!F:F, Raw_AR!A:A, A2, Raw_AR!H:H, "1-30")

Repeat for columns E, F, and G with bucket labels "31-60", "61-90", and "90+" respectively. Copy the entire row 2 down for as many customer rows as you have.

Using ARRAYFORMULA for Efficiency

If your customer list is dynamic (changes frequently), use ARRAYFORMULA in a single cell to fill the entire column at once:

=ARRAYFORMULA(IF(A2:A="", "", SUMIFS(Raw_AR!F:F, Raw_AR!A:A, A2:A, Raw_AR!H:H, "Current")))

This automatically extends to all rows without needing to copy formulas down manually.

Adding a Grand Total Row

At the bottom of the table, add a totals row:

=SUM(B2:B100)

(Adjust the range to match your actual data range.) This gives you the total AR balance by bucket. The number your CFO or auditor wants to see at the top of the aging analysis.

Conditional Formatting for Visibility

Select the 90+ column and apply conditional formatting: Format > Conditional formatting > "Greater than" 0, fill color red. Do the same for the 61-90 column with an orange fill. This gives your collections team an immediate visual cue for priority accounts without any additional work.

Step 4: Automating the Refresh

If you're doing this manually, you're re-exporting from QBO, re-cleaning the CSV, and re-pasting into Raw_AR every time you want updated numbers. For weekly or daily collections reviews, that's a significant time cost.

With a direct connection tool: Tools like brooked.io and G-Accon can refresh the Raw_AR data on a schedule: daily, weekly, or on demand. Because your summary formulas reference the raw data sheet dynamically with SUMIFS, the entire summary table updates automatically when the raw data refreshes. No manual work beyond the initial setup.

With a manual approach: Build a repeatable process. Keep a checklist: export, import to a specific sheet tab (overwrite, don't append), run the cleanup steps. If you do it the same way every time, it takes less than 10 minutes. The formulas handle the rest.

G-Accon's AR Aging Template Approach

G-Accon, the Google Workspace add-on for QuickBooks, has a pre-built AR Aging template that pulls QBO's aging data into Sheets in a more structured format than the raw CSV export.

The template approach gives you:

• Cleaner column headers
• Numbers already formatted as numbers, not text
• Scheduled refresh capability
• Some pre-built pivot logic

Where G-Accon's AR template falls short: it pulls data in a structure close to QBO's native output, which means you still get the vertical-list layout. To get to the columnar 30-60-90 format, you need to layer the SUMIFS formulas described above on top of G-Accon's raw output. The advantage is that G-Accon handles the data extraction cleanly, so you're skipping the CSV cleanup step.

G-Accon plans that include AR aging templates run $25-50/month. If you're already using G-Accon for other QBO reports, the AR template is a natural addition. If AR aging is your only use case, the math may favor a more purpose-built tool.

Using brooked.io for Automated AR Aging

brooked.io is built specifically for accounting and bookkeeping workflows. For AR aging specifically, it:

• Connects directly to QBO via API (no CSV export, no cleanup)
• Syncs invoice-level AR data to a flat table in Google Sheets on your schedule
• Outputs data in a column structure designed to work with standard Sheets formulas from day one: dates as dates, amounts as numbers, customer names as plain text
• Supports multi-client setups for bookkeepers managing several QBO accounts

Once your Raw_AR tab is being populated by brooked.io on a daily schedule, the SUMIFS formulas in your AR_Aging_Summary tab run automatically. Your team opens the sheet every morning to a current view of outstanding AR with aging buckets already calculated.

For bookkeepers managing multiple clients, brooked.io's multi-company support means you can maintain separate AR aging dashboards for each client from a single connected account.

Learn more at brooked.io

Comparison: Manual vs. Automated Approaches

For teams that review AR aging weekly: the one-time formula setup takes 2-3 hours and the manual refresh takes 10-15 minutes each time. Over a year of weekly reviews, that's roughly 10 hours of setup and 13 hours of ongoing refresh work, 23 hours total. At any professional billing rate, a $25-50/month automation tool pays for itself in the first month or two.

Troubleshooting

Problem: My SUMIFS formula returns 0 even though there's data in the raw sheet. Check that the bucket label in your Raw_AR!H column matches the label in your SUMIFS exactly, including capitalization and spacing. "1-30" is different from "1 - 30" or "1-30 Days". Also verify the Amount Due column (column F) contains numbers, not text-formatted numbers.

Problem: The TODAY() function in my Days Overdue formula isn't updating. Google Sheets recalculates NOW() and TODAY() each time the sheet is opened or recalculated. If you're viewing a cached version, try pressing Ctrl+Shift+F9 (Windows) or Cmd+Shift+F9 (Mac) to force full recalculation.

Problem: Some customers appear twice in my summary table. This is usually caused by inconsistent customer name formatting in QBO, extra spaces, different capitalization, or different abbreviations ("Corp" vs "Corporation"). Use TRIM() and UPPER() on the customer name column in your raw data to standardize: =TRIM(UPPER(Raw_AR!A2)).

Problem: Invoices with partial payments are showing the wrong amount. Make sure Column F in your Raw_AR sheet represents the remaining open balance (Original Amount minus Amount Paid), not the original invoice total. QBO's export includes both; you want the "Open Balance" or "Amount Due" column, not the "Total" column.

Problem: The UNIQUE formula for the customer list picks up blank rows. Wrap your UNIQUE formula in a FILTER to exclude blanks: =SORT(UNIQUE(FILTER(Raw_AR!A2:A, (Raw_AR!A2:A<>"")*(Raw_AR!F2:F<>0)))).

Problem: After the sync refreshes, my conditional formatting disappeared. Conditional formatting is tied to cell ranges, not data values. If the refresh overwrites the raw data sheet in a way that shifts the data range (e.g., a different number of rows), re-apply the formatting. To avoid this, apply conditional formatting to the Summary sheet, not the raw data sheet. The summary data doesn't change structure, only values.

Problem: My aging buckets are off by a day. This is usually a timezone issue. TODAY() returns the date in the spreadsheet's timezone, which you set under File > Settings. If your QBO account is in a different timezone than your spreadsheet, due dates near midnight can calculate as one day off. Set both to the same timezone.

Bottom Line

QuickBooks Online's AR aging report is useful for a quick check within the app. It is not useful for collections workflows, CFO reporting, audit support, or any situation where people need to sort, filter, and act on the aging data.

The gap is real, and it's easily filled with a combination of:

1. Getting invoice-level AR data into Google Sheets (manually or automatically)
2. Calculating days overdue with a simple formula
3. Assigning bucket labels with nested IF statements
4. Aggregating by customer and bucket with SUMIFS

The one-time setup takes a few hours. After that, the formulas do the work automatically every time the data refreshes.

If you want to skip the manual refresh entirely, connect QBO to Sheets with a tool that handles the sync automatically. The formulas stay exactly the same. You just stop doing the export-import cycle by hand.

Get Automated AR Aging with brooked.io

brooked.io connects QuickBooks Online to Google Sheets and keeps your AR data current on a schedule. No more CSV exports. No more cleanup. Your aging buckets are always up to date.

Start your free trial at brooked.io

Related Articles

• QuickBooks Not on Advanced? Here's How to Get Your Data into Google Sheets Anyway
• How to Build a Collections Dashboard in Google Sheets from QuickBooks Data
• QuickBooks Online P&L to Google Sheets: Month-over-Month Comparison

Troubleshooting quick reference

Frequently asked questions