Are you struggling to filter data by month and year in Google Sheets? Don’t worry, it’s a common task that many users find confusing. In this tutorial, we will explore how to use the Filter and Query functions to achieve this effortlessly.
Introduction to Filter by Month and Year in Query in Google Sheets
Filtering data based on dates can be a bit tricky, especially when using the Query formula. One important thing to note is that when a QUERY formula refers to a date in a cell, that date should be converted to a string either within the formula or by creating a new cell specifically for the converted date.
For example, if Cell A1 contains the date criteria, you can convert it to a string in Cell B1 and refer to B1 in the formula. Alternatively, you can use Cell A1 itself as the criteria by converting it to a string within the Query formula.
In this tutorial, we will focus on the compact method of date conversion. However, if you’re interested in learning the long-winded approach, you can check out this guide.
Tips to Use Date Criteria in Query
Let’s start with some simple examples to understand how to use date criteria in the Query function.
Suppose you want to filter data in column A based on a specific date, let’s say January 26, 2018. The formula would look like this:
=query(A1:A, "Select * where A = date '2018-01-26'", 1)If you want to use a cell reference as the date criterion, you can do so by converting the date to a text string using the compact method. Here’s an example:
=query(A1:A, "Select * where A = date '"&text(D1, "YYYY-MM-DD")&"'", 1)In both examples, you can see that the date is converted to a text string within the Query formula. The compact method allows for a more streamlined and efficient approach to date conversion.
How to Filter by Month and Year in Query in Google Sheets
Let’s dive into the tutorial section now. Before we begin, let me explain a few tips that will help you understand the concepts better.
- How to filter by date duration in Query?
- How to filter by month in Query?
- How to filter by year in Query?
To illustrate these concepts, we will use two sample sheets: Sheet1 and Sheet2. Sample data is provided in the images below:
Now, let’s explore the formulas that will allow you to filter data based on month and year in Query.
1. How to filter by date duration in Query?
Suppose you want to filter the sample data in Sheet1 based on a date range. You can use the following formula:
=query(A1:C, "Select * where A >= date '2018-01-01' and A <= date '2018-01-31'", 1)If you prefer to use cell references as the date criteria, you can modify the formula as follows:
=query(A1:C, "Select * where A >= date '"&text(D1, "YYYY-MM-DD")&"' and A <= date '"&text(E1, "YYYY-MM-DD")&"'", 1)In the modified formula, we refer to cells D1 and E1 for the start and end dates, respectively. This allows for more flexibility and dynamic filtering.
2. How to filter by month in Query?
To filter data by month, you can use the following formulas:
=query(A1:C, "Select * where month(A) = month(date '2018-01-31')", 1)=query(A1:C, "Select * where month(A) = 0", 1)Similarly, if you prefer to use cell references as the month criterion, modify the formula accordingly:
=query(A1:C, "Select * where month(A) = month(date '"&text(D1, "YYYY-MM-DD")&"')", 1)3. How to filter by year in Query?
Filtering data by year is similar to filtering by month. Just replace the “month” function with the “year” function in the formulas mentioned above.
Now, let’s move on to the formula that allows you to filter by both month and year.
The Formula to Filter by Month and Year in Query in Google Sheets
It’s always recommended to filter data by both month and year to ensure accurate results. Filtering by month alone may not give you the desired outcome, as it may include data from different years.
Here’s a formula that combines filtering by month and year:
=query(A1:C, "Select * where month(A) = month(date '2018-01-31') and year(A) = year(date '2018-01-31')", 1)Alternatively, you can use a simpler version of the formula:
=query(A1:C, "Select * where month(A) = 0 and year(A) = 2018", 1)If you prefer to use cell references as the month and year criteria, you can use the master 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)This formula is flexible and allows you to retain the column header from the selected sheet. It’s especially useful when filtering data from multiple sheets based on user-selected criteria.
Popup Data Based on Drop-Down Selection from Multiple Sheets
In this section, we will explore a different method of filtering data by month and year in Query. We will use drop-down menus to select the sheet name, month, and year criteria.
Here’s an example of how to create a drop-down menu:
- Go to Cell A2 and select “Data” from the menu.
- Choose “Data validation.”
- Set the validation criteria to contain the sheet names so that you can select from this menu.
Similarly, create drop-down menus for month and year criteria in Cells B2 and C2, respectively.
Now, enter the following 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)This formula allows you to filter data based on the selected sheet, month, and year. It retains the column header from the selected sheet, even if the column names differ across sheets.
And there you have it! You’ve learned how to filter data by month and year in Query using various techniques.
To practice and explore further, you can copy the Query Example Sample Sheet.
I hope you found this tutorial helpful. If you have any questions or want to learn more about Google Sheets, visit Crawlan.com for additional resources. Enjoy filtering your data effortlessly!
