Let’s learn how to use the Query Quarter function in a non-calendar fiscal year data in Google Sheets.

The Quarter() function in Google Sheets Query is for summarizing or for extracting data based on the calendar fiscal year, not based on the non-calendar fiscal year. This’s is an important point that you must remember.

A fiscal year (FY) is a one year period for financial reporting that can be different from a calendar year in some countries.

The calendar fiscal year is from January to December whereas the non-calendar fiscal year has a different start/end month.

In some countries, it’s from April-March, and in some other countries, it’s from July-June (all whole months).

I’ve also seen the fiscal year in use from 8th July to 7th July which I’m ignoring in this tutorial for the sake of simplicity.

As you may already know, there are four quarters in a financial year and they are Q1, Q2, Q3, and Q4.

In Google Sheets Query Quarter(), Q1 is from Jan to Mar, Q2 is from Apr to Jun, Q3 is from Jul-Sep, and Q4 is from Oct-Dec.

Let’s see how to change the quarters (quarter periods) in the Quarter() function in Google Sheets Query. It’s essential to learn the use of Query Quarter function in a non-calendar fiscal year dataset in Google Sheets.

First I’m going to explain to you how to use the Quarter() without changing the periods, i.e. in a calendar year.

Query Quarter() – In Calendar (Fiscal) Year

I am going to give you two formulas – one for summarizing the data based on quarters and the other for extracting the rows from the data that also based on quarters.

Sample Data:

The sample data for testing the Query Quarter function in the calendar year is spread across 5 columns and 25 rows.

You may not want to spend your time to type it, right? Then please copy the sheet from the sample sheet link at the end.

Since the Quarter() function is for the calendar year, we can directly use it in data manipulation. Here are the said two formulas.

Populate Data Based on Quarter (Calendar FY)

In cell G2 enter 1 and in cell H2 insert the following Query formula.

=query(
     A1:E,
     "Select * 
     where quarter(A)="&G2&""
)

The formula would extract the rows that containing the dates in column A fall in the period Jan to Mar.

Change the number in cell G2 to 2 to get the rows containing the dates in the period Apr to Jun. Change the criterion (quarter) in cell G2 to 3, then 4 and see the outputs.

In the following example, we can summarize the data based on calendar year quarters. After that, you can learn how to use the Query Quarter() function in a non-calendar fiscal year data.

Summarize Data Based on Quarters (Calendar FY)

Use the below formula for Quarter wise, product wise summary.

=query(
     A1:E,
     "Select quarter(A),B,sum(E) 
     where A is not null 
     group by quarter(A),B"
)

In my shared example sheet, the above formula is keyed in cell O2 in “FY Jan Dec” sheet.

Query Quarter() – Non Calendar Fiscal Year

Can we use the Query Quarter() function in a non-calendar fiscal year and if YES, how?

When considering the above two formulas, here, in this case, we require a helper column for the second one (summary).

For extracting/populating rows, the helper column is not necessary.

Sample Data:

Please only include the date range as per FY. Here the FY is Apr-Mar. So, in the sample data, I’ve included the transactions that took place during this period.

Populate Data Based on Quarter (FY Apr-Mar and FY Jul-Jun)

We have the quarter number (criterion) in cell G2 (in example sheet “FY Mar Apr”).

We must virtually convert this non-calendar year quarter to the calendar year quarter using IF, IFS, or SWITCH. I’m using the Switch function and I’ll explain the same below.

For example, the fiscal year in my country is from Apr to Mar. So Q1 will be from Apr-Jun, Q2 will be from Jul-Sept, Q3 will be from Oct-Dec, and Q4 will be from Jan to Mar.

As per the Query Quarter() function, the Q1 is from Jan-Mar, Q2 is from Apr-May, Q3 and Q4 follow this pattern. Now see the data in the below table.

Table 1

Legend: * Read as 1-Jan-2020 to 31-Mar-2020 for column 3 (Quarter as per FY Apr-Mar).

Using the Switch function we can change the criterion in cell G2 virtually. I mean when you type 1 Query will read it as 2.

I mean the conversion of quarters from Apr-Mar FY to the quarters in a calendar year. It is like 4 to 1, 1 to 2, 2 to 3, and 3 to 4 (please refer to the above table).

Here is that formula.

=switch(G2,4,1,1,2,2,3,3,4)

Let me now show you how to include this in Query as the criterion. Here you go!

=query(
     A1:E,
     "Select * 
     where quarter(A)="&switch(G2,4,1,1,2,2,3,3,4)
)

The above is an example of the use of the Query Quarter function in a non-calendar fiscal year data in Google Sheets.

If the FY is Jul-Jun, then the use the below SWITCH formula (after the table).

Table 2

Legend:

* Read as 1-Jan-2020 to 31-Mar-2020 for column 3.
** Read as 1-Apr-2020 to 30-Jun-2020 for column 3.

=switch(G2,3,1,4,2,1,3,2,4)

Summarize Data Based on Quarter (FY Apr-Mar and FY Jul-Jun)

When we use Query to summarize a non-calendar fiscal year data, we may better to use a helper column.

For FY Apr-March, in cell F1, key the below formula in.

={"Quarter";
ArrayFormula(
     if(
        Len(A2:A),
        switch(ROUNDUP(month(A2:A)/3,0),1,4,2,1,3,2,4,3),
     )
)
}

For FY Jul-Jun, in cell F1, key the below formula in.

={"Quarter";
ArrayFormula(
     if(
        Len(A2:A),
        switch(ROUNDUP(month(A2:A)/3,0),1,3,2,4,3,1,4,2),
     )
)
}

Before going to the summary, let men explain the above formula. Only the Switch part is important.

The IF and LEN are for just limiting the last row up to the cell A25 which is the last non-empty cell in the data.

The ROUNDUP function coverts the dates in column A to quarters as per calendar year. You can find those tips in detail here – Extract Quarter from a Date in Google Sheets.

The Switch function converts that quarter numbers as per your required FY. Please refer to tables 1 and 2 for converting calendar fiscal year quarters to non-calendar fiscal year quarters.

Formula for Summary:

=Query(
     A1:F,
     "Select F,B,sum(E) 
     where A is not null 
     group by F,B"
)

Since we are using a helper column, the Quarter() is not required.

That’s all about how to use the Query Quarter function in a non-calendar fiscal year data in Google Sheets. Enjoy!

sample_data_1620

Resources:

• Month, Quarter, Year Wise Grouping in Pivot Table in Google Sheets.
• Formula to Group Dates by Quarter in Google Sheets.
• Query to Create Daily/Weekly/Monthly/Quarterly/Yearly Report Summary in Google Sheets.