By following our table formatting and aging intervals, you can easily create an age analysis report using the QUERY function in Google Sheets.

The QUERY function in Google Sheets is invaluable for categorizing and evaluating data based on age-related parameters.

Age analysis serves critical purposes, such as identifying overdue invoices and gaining insights into the age distribution of a customer base.

In this example, we will specifically focus on identifying overdue invoices using age analysis. We’ll utilize the following aging buckets:

• Current: Invoices that are not past due.
• 1-30 Days: Invoices that are 1-30 days past due.
• 31-60 Days: Invoices that are 31-60 days past due.
• 61-90 Days: Invoices that are 61-90 days past due.
• 90+ Days: Invoices that are more than 90 days past due.

These intervals, referred to as aging buckets, are essential for systematically assessing the timeliness of payments or the age of items.

Data Formatting for Age Analysis – For QUERY Function Use

The table should consist of 5 columns (A to E) in the following format:

Customer Name | Invoice # | Invoice Date | Due Date | Amount Due

In the first row of the 6th column (F1), use the following formula to assign aging buckets corresponding to due dates in column D:

=VSTACK("Intervals", ArrayFormula(LET(dd, D2:D, tdys, DAYS(TODAY(), dd), IF(dd="", ,IF(tdys<1, "1] Current", IF(tdys<31, "2] 1-30 days", IF(tdys<61, "3] 31-60 days", IF(tdys<91, "4] 61-90 days", "5] 90+ days"))))))))

The data formatting is now prepared. This setup will facilitate age analysis using the QUERY function.

Download Sample Sheet

Since providing current data might result in everything becoming more than 90 days past due as time passes, I’ve included evergreen sample data in the shared sheet for testing purposes.

QUERY Formula for Identifying Overdue Invoices in Google Sheets

The sample data is located in columns A to F on the “Overdue Invoices” sheet. If your sheet name differs, double-click the tab name and rename it accordingly as “Overdue Invoices,” as the formula references this sheet.

On a second sheet named “Age Analysis,” input the following QUERY formula into cell A1:

=ArrayFormula(QUERY(VSTACK(QUERY('Overdue Invoices'!A1:F, "SELECT A, B, C, D, SUM(E) WHERE A IS NOT NULL GROUP BY A, B, C, D PIVOT F", 1), QUERY(QUERY({IF('Overdue Invoices'!A1:A<>"", 'Overdue Invoices'!A1:A & " Total",""), 'Overdue Invoices'!B1:F}, "SELECT Col1, 1/0, 10/0, 100/0, SUM(Col5) WHERE Col1<>'' GROUP BY Col1 PIVOT Col6", 1)," OFFSET 1", 0)), "SELECT * ORDER BY Col1 ASC", 1))

This QUERY formula generates the age analysis report.

Do you have multiple customers in the source data? Don’t worry. The formula will insert subtotals in the report.

The result includes up to 9 columns as follows:

Customer Name | Invoice # | Invoice Date | Due Date | 1] Current | 2] 1-30 days | 3] 31-60 days | 4] 61-90 days | 5] 90+ days

If you prefer a report with only the customer name, due date, and age columns, adjustments can be made as explained below.

Removing Invoice Number and Date Columns from the Age Analysis Report

Below are the formula parts that select and group those columns:

SELECT A, B, C, D,
GROUP BY A, B, C, D
SELECT Col1, 1/0, 10/0, 100/0,

If you want to remove the second column that contains invoice numbers, the above parts become as follows:

SELECT A, C, D,
GROUP BY A, C, D
SELECT Col1, 10/0, 100/0,

If you want to remove column B (invoice number) and column C (invoice date) and keep only the customer name and due date, make the changes as follows:

SELECT A, D,
GROUP BY A, D
SELECT Col1, 100/0,

The rule of thumb is that there should be at least two columns: one for the customer name and another column such as invoice number, invoice date, or due date.

These fine-tuning adjustments will help you customize your age analysis reports according to your requirements.

Therefore, the QUERY function is a powerful option to automate age analysis in Google Sheets.

Formula Explanation

This section covers the formulas used for assigning aging intervals in column F and the QUERY formula that generates the age analysis report.

This is optional, and you can skip this part.

Formula for Inserting Aging Buckets in Column F (Overdue Invoices Sheet)

The formula in cell F1 of the “Overdue Invoices” sheet is a nested IF formula:

=VSTACK("Intervals", ArrayFormula(LET(dd, D2:D, tdys, DAYS(TODAY(), dd), IF(DD="", ,IF(tdys<1, "1] Current", IF(tdys<31, "2] 1-30 days", IF(tdys<61, "3] 31-60 days", IF(tdys<91, "4] 61-90 days", "5] 90+ days"))))))))

The DAYS function returns the number of days from the due date of the invoice to today’s date and the LET function names it tdys:

DAYS(TODAY(), dd)

• where dd is the assigned name for the due date.

Here’s the breakdown of the formula:

• IF(dd="", , – Returns an empty string if dd (due date) is empty.
• IF(tdys<1, "1] Current", – Returns “1] Current” if today – due_date (tdys) is less than 1.
• IF(tdys<31, "2] 1-30 days", – Returns “2] 1-30 days” if today – due_date is between 1 and 30, inclusive.
• IF(tdys<61, "3] 31-60 days", – Returns “3] 31-60 days” if today – due_date is between 31 and 60, inclusive.
• IF(tdys<91, "4] 61-90 days", – Returns “4] 61-90 days” if today – due_date is between 61 and 90, inclusive.
• "5] 90+ days") – Returns “5] 90+ days” in all other cases.

This formula is essential for creating the age analysis in Google Sheets, whether using the QUERY function or a Pivot Table.

QUERY Formula That Generates the Age Analysis

In cell A1 in the “Age Analysis” sheet, we have used the following QUERY formula:

=ArrayFormula(QUERY(VSTACK(QUERY('Overdue Invoices'!A1:F, "SELECT A, B, C, D, SUM(E) WHERE A IS NOT NULL GROUP BY A, B, C, D PIVOT F", 1), QUERY(QUERY({IF('Overdue Invoices'!A1:A<>"", 'Overdue Invoices'!A1:A & " Total",""), 'Overdue Invoices'!B1:F}, "SELECT Col1, 1/0, 10/0, 100/0, SUM(Col5) WHERE Col1<>'' GROUP BY Col1 PIVOT Col6", 1)," OFFSET 1", 0)), "SELECT * ORDER BY Col1 ASC", 1))

It’s actually a nested QUERY formula that involves four formulas. These are:

Formula 1:

=QUERY(VSTACK(QUERY('Overdue Invoices'!A1:F, "SELECT A, B, C, D, SUM(E) WHERE A IS NOT NULL GROUP BY A, B, C, D PIVOT F", 1)))

This QUERY formula returns the age analysis report without the subtotal for each customer.

Formula 2:

=ArrayFormula(QUERY({IF('Overdue Invoices'!A1:A<>"", 'Overdue Invoices'!A1:A & " Total",""), 'Overdue Invoices'!B1:F}, "SELECT Col1, 1/0, 10/0, 100/0, SUM(Col5) WHERE Col1<>'' GROUP BY Col1 PIVOT Col6", 1))

This returns the subtotal rows but with a header row.

Formula 3:

=ArrayFormula(QUERY(formula_2)," OFFSET 1", 0))

This removes the header row from Formula 2.

We used the VSTACK function to vertically stack Formula 1 and Formula 3 and used it as the data in the fourth QUERY.

Formula 4:

=ArrayFormula(QUERY(VSTACK(formula_1, formula_3), "SELECT * ORDER BY Col1 ASC", 1))

This selects all columns and sorts the customer column in ascending order. This sorting places the subtotal rows in the correct places.

Resources

• Create Age Analysis Report Using Google Sheets Pivot Table
• Custom Heat Map in Google Sheets for Age Analysis
• Create an Ageing Analysis Report Using Google Sheets Formulas