If you’re using Google Sheets to summarize sales reports and need to filter dates that fall in the current quarter or the previous quarter, I’ve got the solutions you need.
Query to Filter Dates in the Current Quarter (Calendar Year)
If you follow the calendar year (January 1st to December 31st), you can use the QUARTER() scalar function in the QUERY formula to filter data. Here’s an example:
=query(A1:C, "Select A,B,C where quarter(A)=quarter(now()) and year(A)=year(now())", 1)This formula will filter the data based on the current quarter. You can insert it in any blank column to pull a three-column output.
Please note that the current quarter in the above result is based on my system date. If you’re reading this tutorial at a future date, you may get a different result based on the current quarter on that date.
To summarize the sales data, you can use the above output as the data in another QUERY formula:
=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 in the Last Quarter (Calendar Year)
To filter dates in the previous quarter in a calendar year, you can use the same formula as above but replace quarter(now()) with quarter(now())-1. This applies to both the formulas mentioned earlier.
Current Quarter and Previous Quarter Calculations – Dynamic Formula
If you’re not following the calendar year, we need to find another way to filter dates in Google Sheets. We can use a dynamic formula that assigns quarters based on the fiscal year prevailing in your company.
Here’s how you can set it up:
- Specify the fiscal year ending month name in cell F1 and the year in cell G1.
- Use the following formula in cell D1:
=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))})This formula will assign quarters dynamically based on the fiscal year. You can then use the helper column D to filter data based on the current quarter or the previous quarter.
To filter the data based on the current quarter, you can use the FILTER formula instead of QUERY:
=FILTER(A2:C,((D2:D>0)*(D2:D=vlookup(eomonth(today(),0),{eomonth(A2:A,0),D2:D},2,0))))To filter the data based on the previous quarter, replace vlookup(eomonth(today(),0),{eomonth(A2:A,0),D2:D},2,0) with vlookup(eomonth(today(),0),{eomonth(A2:A,0),D2:D},2,0)-1.
These formulas will effectively filter the data based on the current quarter or the previous quarter in a fiscal year in Google Sheets.
That’s it! Now you have the tools to filter dates in the current quarter and the previous quarter in Google Sheets. Enjoy using these formulas to analyze and summarize your sales data. If you need further assistance with Google Sheets or any other data-related topics, feel free to visit Crawlan.com.
