I want to filter dates that fall in the current quarter or last/previous quarter in Google Sheets for summarizing a sales report.

How to do it in Google Sheets?

If this is what you want to ask, I’ve solutions.

Such types of filtering data may be simple if you follow the calendar year (1st January to 31st December) as we can use the QUARTER() scalar function in QUERY.

But if you follow a fiscal year, e.g., 1st April to 31st March, the said QUERY scalar function won’t help you.

So the solution to filter the current quarter or previous quarters is to create a dynamic quarter column filled with quarter numbers based on your chosen fiscal year ending.

Before proceeding to a dynamic formula that may work irrespective of the calendar or fiscal year, here are the Query formulas to filter the current quarter or previous quarter in a calendar year in Google Sheets.

Query to Filter Dates Fall in Current Quarter in Google Sheets (Calendar Year)

My sample data is in the range A1:C.

In this, the cells A1, B1, and C1 contain the column names “Date,” “Product,” and “Amount,” respectively, and the corresponding data below them.

=query(A1:C,"Select A,B,C where quarter(A)=quarter(now()) and year(A)=year(now())",1)

We can insert the above QUERY formula in the very first row of any blank column to pull a three-column output.

First empty E1:G, if you are going to insert it in cell E1.

Note:-

• The current quarter in the above result is calculated based on my system date when writing this post.
• If you are reading this tutorial at a future date, you may get a different result based on the current quarter on that date.

We can use the QUERY function as above to filter data in a calendar year for the current quarter in Google Sheets.

The end goal is probably to summarize the above sales data.

If so, use the above output as the data in another QUERY.

Summary:

=query(query(A1:C,"Select A,B,C where quarter(A)=quarter(now()) and year(A)=year(now())",1),"Select Col2,sum(Col3) group by Col2",1)

Query to Filter Dates Fall in Last Quarter in Google Sheets (Calendar Year)

Here, you should replace quarter(now()) in the formulas with quarter(now())-1.

It applies to both the above formulas.

I am not including a screenshot, as at the time of writing this post, today’s date falls in the first quarter of the calendar year.

So, there won’t be any data to filter in the last quarter.

Follow the above instructions to filter dates in the previous quarter in a calendar year in Google Sheets.

Current Quarter and Previous Quarter Calculations – Dynamic Formula

In financial reporting/budgeting, if you are not following the calendar year (1st January to 31st December), then do not use the above formulas.

We should find another way to filter dates that fall in the current quarter or previous quarter in Google Sheets.

Data Preparation

We should find a way to ‘dynamically’ assign quarters based on the fiscal year prevailing in our company.

What do you mean by the term ‘dynamically’ in the above context?

In a non-dynamic way, we first assign quarters based on the calendar year. Then, switch them into fiscal years, for example, by replacing 1 (Q1) with 4 (Q4) if the fiscal year is April – March.)

You can find those details here – Query Quarter Function in Non-Calendar Fiscal Year Data (Google Sheets).

Here is a better (dynamic) solution that we will use.

In this, you are only required to specify the fiscal year ending month name (F1) and year (G1). The formula in cell D1 will take care of the rest.

=ArrayFormula({"Quarter";ifna(vlookup(eomonth(A2:A,0),{eomonth(EDATE(eomonth(edate(eomonth(date(G1,month(F1&1),1),0),-12),0)+1,SEQUENCE(12,1,0,1)),0),value(flatten(SUBSTITUTE(sequence(4,1)," ","",SEQUENCE(1,3))))},2,0))})

We can use the helper column D to filter data based on the current quarter or previous quarter in Google Sheets.

For the D1 formula explanation, please check this guide – Convert Dates To Fiscal Quarters in Google Sheets.

If you input March 2022 in (F1:G1), the fiscal year will be from 1st April 2021 to 31st March 2022.

If you input December 2022 in (F1:G1), the fiscal year will be the calendar year from 1st Jan 2022 to 31st Dec 2022.

Feed any month and year in F1 and G1. The formula will take care of the quarters in column D.

Two Filter Formulas

Current Quarter

We have a quarter column now. How to filter the data range A1:C based on the current quarter in Google Sheets?

Here we will use FILTER instead of QUERY.

=FILTER(A2:C,((D2:D>0)*(D2:D=vlookup(eomonth(today(),0),{eomonth(A2:A,0),D2:D},2,0))))

How does this formula work?

I have used two conditions in FILTER.

Condition 1 – D2:D>0

Condition 2 – D2:D=vlookup(eomonth(today(),0),{eomonth(A2:A,0),D2:D},2,0)

Here the VLOOKUP searches the end of the current month’s date in A2:A (in this column, converted all the dates to end of the month dates) and returns the corresponding quarter number from column D.

That will be the quarter number of the current month.

Summary:

For summarising the sales, you can use another Query as earlier.

=query(filter(A2:C,((D2:D>0)*(D2:D=vlookup(eomonth(today(),0),{eomonth(A2:A,0),D2:D},2,0)))),"Select Col2,sum(Col3) group by Col2",0)

There is one change here, and that is in the Query header.

It is 0 (zero) here as the filter result has no header row.

What about filtering the previous quarter in a fiscal year in Google Sheets?

Last Quarter

Here, you should put -1 in condition two as follows D2:D=vlookup(eomonth(today(),0),{eomonth(A2:A,0),D2:D},2,0)-1.

Follow the above instructions to filter dates in the last/previous quarter in a fiscal year.

That’s all. Thanks for the stay. Enjoy!