Do you want to filter data in Google Sheets to show values within a specific range of dates? Look no further! In this tutorial, we will explore how to filter by date range using the powerful Query function in Google Sheets.
Understanding the Google Sheets Query Function
Before we dive into the example, let’s quickly understand how the Google Sheets Query function works. The Query function allows you to use data commands to manipulate and filter data in Google Sheets. It replaces multiple functions, making it a versatile tool for data analysis.
To use the Query function, you need to provide three arguments:
- Data range: The range of data you want to analyze.
- Query: The query you want to run, enclosed in quotation marks.
- Headers: An optional number indicating the number of header rows in your data.
Now, let’s move on to filtering data by date range using the Query function.
Filtering by Date Range in Google Sheets
To demonstrate how to filter data by date range in Google Sheets, we’ll use real-world examples.
Step 1: Define the Date Range
First, determine the date range you want to filter from your data. For example, let’s filter all dates prior to the year 2021.
Step 2: Writing the Query Formula
To start the query formula, select an empty cell (e.g., E2) and enter the formula =QUERY. A blue question mark will appear, detailing the arguments required for the function.
Next, input the arguments for the Query function. Set the data range as A1:C8, which is our first argument. Then, input the query as "select * where A < date '"&TEXT(DATEVALUE("1/1/2021"),"yyyy-mm-dd")&"'", which serves as our second and third arguments. Remember to close the formula with a closing bracket.
Step 3: View the Filtered Results
Press Enter, and voila! The rows before the year 2021 will be displayed, filtered based on your specified date range.
Important Note!
Please note that the data returned from the Query function will not be formatted. You can customize the data as per your preferences.
Wrapping Up
And that’s all there is to it! You now know how to filter data by date range in Google Sheets using the Query function. Feel free to copy the example spreadsheet below to see it in action. Remember, the most important thing is to have fun while working with data.
Example Spreadsheet: Make a copy of the example spreadsheet
If you’re hungry for more Google Sheets tutorials, be sure to check out the wealth of resources available at Crawlan.com. Happy filtering!
