Filter by Month and Year in Query in Google Sheets

You must know how to filter by month and year in any Spreadsheet applications. This task is one of the most commonly occurring one. In Google Sheets, you can use either the Filter or the Query function for this. In this Google Sheets tutorial, you can learn how to filter by month and year in Query.

Here I am digging deep into a very detail part of the Query use with date criteria. Because the date criteria are one of the complex parts in Query or at least Google Sheets users think so.

Further, I prefer Query to Filter as the former retains the column name in the output.

Introduction to Filter by Month and Year in Query in Google Sheets

The Date Criterion in Query is one of the most confusing parts. The thing is that when a QUERY formula refers to a date in a cell, that date should be converted to string either within the formula or first convert the date in a new cell and refer to that cell.

For example, if Cell A1 contains the date criteria, convert this to string in Cell B1 and refer B1 in the formula. Also, you can use the Cell A1 itself as criteria by converting it to string within the Query formula.

You can either follow the Long-winded Approach or the compact form to convert a date to the string in Google Sheets Query. I am following here the Compact method.

I’ll explain the Compact method of date conversion below. If you want to learn the Long-winded Approach, follow the below link.

Must Read: Convert Date to String Using the Long-winded Approach

Tips to Use Date Criteria in Query

Suppose I want to filter column A which contain dates. The formula would be like this.

Query Formula 1:

=query(A1:A,"Select * where A=date'2018-1-26'",1)

This formula is to filter column A for the rows contain the date 26/01/2018. Now I want to use a cell reference as date criterion in this Query formula. I have got the criterion in Cell D1 as 26/01/2018. See the formula.

Query Formula 2:

=query(A1:A,"Select * where A=date'"&text(D1,"YYYY-MM-DD")&"'",1)

From the above two Query formula examples to date criteria, you can understand one thing. The date should be converted to a text string in Query to use as criteria. In the second formula, I’ve converted the date to text using the Compact method.

In the first formula, the date is used itself as a string in Query. You can see the date within single quotes.

Having said that I am taking you to the tutorial section. Here we go.

How to Filter by Month and Year in Query in Google Sheets

First, let me explain the below 4 tips. Then I will explain to you how to filter by month and year in Query in Google Sheets.

1. How to filter by date duration in Query?

2. How to filter by month in Query?

3. How to filter by year in Query?

In any spreadsheet formula examples, the essential part is some kind of sample data, here is no exception.

I have two sheets with sample data. For the first few examples, the Sheet1 is only required. But at the last part, I am going to apply the Query filter by month and year in a drop-down. There I am fetching data from different sheets. The second sheet (Sheet2) is required then.

Sheet1:

Sample Data Sheet 1 - Filter by Month and Year in Query

Sheet2:

Sample Data Sheet 2 - Filter by Month and Year in Query

Here are some formula examples that can help you understand how to use dates in Query as criteria.

1. How to filter by date duration in Query?

The following two formulas would filter the above sample data in Sheet1 based on the dates in Column 1.

Here the date criteria are based on the Query Formula 1.

=query(A1:C,"Select * where A>=date'2018-1-1' and A<=date'2018-1-31'",1)

The below date criteria are based on the Query Formula 2.

=query(A1:C,"Select * where A>=date'"&text(D1,"YYYY-MM-DD")&"' and A<=date'"&text(E1,"YYYY-MM-DD")&"'",1)

In the above Google Sheets Query formula, I have the date criteria in Cell D1 and E1. In D1, I have a start date and in D2 an end date. The formula would filter the above sample data between these dates, both the dates inclusive.

2. How to filter by month in Query?

As per Query Formula 1.

=query(A1:C,"Select * where month(A)=month(date'2018-1-31')",1)

Here is a simple version. Here 0 indicates January. You can put 1 for February, 2 for March and so on.

=query(A1:C,"Select * where month(A)=0",1)

As per Query Formula 2.

=query(A1:C,"Select * where month(A)=month(date'"&text(D1,"YYYY-MM-DD")&"')",1)

3. How to filter by year in Query?

Here I’m skipping the formulas. Just change the month in above formulas to year. That’s all you want to do to filter by month in Query in Google Sheets.

4. The Formula to Filter by Month and Year in Query in Google Sheets

Even if you want month wise filter, I suggest you to always go for month and year wise filter. Because a month wise filter may bring wrong outputs. For example, if you filter column A for the month of January, it may return records containing the month of January irrespective of year.

Query Formulas to Filter by Month and Year in Google Sheets

The formula that based on the Query Formula 1.

=query(A1:C,"Select * where month(A)=month(date'2018-1-31') and year(A)=year(date'2018-1-31')",1)

See the simpler and most common use.

=query(A1:C,"Select * where month(A)=0 and year(A)=2018",1)

The formula that based on the Query Formula 2. I wish to call it the master formula.

Master Query Formula:

=query(A1:C,"Select * where month(A)=month(date'"&text(D1,"YYYY-MM-DD")&"') and Year(A)=Year(date'"&text(D1,"YYYY-MM-DD")&"')",1)

Hope you have learned how to filter data by month and year in Google Sheets. Now see one advanced and very useful tip using the above formula.

I have shared the screenshots of two sample sheets above. Both the sheets contain similar data.

Now in a third sheet, I want to filter data by month and year wise. But the criteria are in the form of drop-down menu items. I have got a drop-down menu as below.

Popup Data Based on Drop-Down Selection from Multiple Sheets

The below example also shows how to filter by month and year in Query in Google Sheets but in a different way.

See this image. In A2, B2, and C2 I’ve different drop-down menus for selecting Sheet Name, Month and Year respectively.

Drop-down menu in Query Date Filter

Hope you already know how to create a drop-down menu in Google Sheets. It’s using the data validation. If not see that below.

Go to Cell A2. Then from the “Data” menu select “Data validation”.

Data Validation for Drop-down menu in Query

The above settings will create a drop-down menu in Cell A2 containing the Sheet names. So you can select the Sheet names from this menu.

Similarly, create drop-down menus in B2 for Month and C2 for Year.

After that enter this formula in Cell A4.

=query(ArrayFormula({indirect(A2&"!A1:C1");indirect(A2&"!A2:C")}),"Select * where month(Col1)=month(date '"&text(date(C2,month(B2&1),1),"yyyy-mm-dd")&"') and Year(Col1)=year(date '"&text(date(C2,month(B2&1),1),"yyyy-mm-dd")&"')",1)

Now select the Sheet Name, Month and Year. The formula will filter your data by Month and Year from the selected sheet. Also, this formula is so flexible as it retains the column header from the selected Sheet. If your Sheet1 and Sheet2 have different column names, this formula can correctly return that titles.

Please refer my sample data in Sheet 1 and Sheet 2 (you can see the screenshots above).

Here is my Sample Sheet. It’s in copy mode. Test it there.

Copy The Query Example Sample Sheet.

Formula Explanation

I am not going into the detail of this formula as it can confuse you. Still here is some basic info.

This formula is in line with the Master Query Formula above. But there are minor changes.

Here we are applying the formula in a third sheet. Our range in Query is based on the sheet name that we select from the drop-down in A2. So instead of the range A1: C, I have used the Indirect function to refer to the concerned Sheet and range. I am talking about the second Indirect in the formula.

At the beginning of the formula, there is one more Indirect. That I’ve used to get the header row flexibly. Because my header rows are different in Sheet1 and Sheet2.

Finally, the Month is in Text in the drop-down menu in B2. I’ve converted it to the number within Query.

That’s all. Hope you have enjoyed this tutorial on how to filter by month and year in Query in Google Sheets. Thanks for the stay. Enjoy!