Table of Contents
Filtering a Google Sheets Query by date range is a simple and convenient way to view and analyze data from a specific period of time. To filter by date range, use the WHERE clause in the query to specify the date column and the desired date range. This will filter the query results to only include data within the specified date range. Additionally, you can also use the SORT BY clause to sort the results by the date column.
You can use the following formula to filter by a date range in a Google Sheets query:
=QUERY(A1:C9,"select * where A > date '"&TEXT(DATEVALUE("1/1/2020"),"yyyy-mm-dd")&"'")
This particular formula returns all rows in the range A1:C9 where the date is after 1/1/2020.
The following examples show how to use this formula in practice with the following dataset:
Example 1: Filter Rows Before Some Date
We can use the following formula to filter for rows where the date is before 1/10/2020:
=QUERY(A1:C17,"select * where A < date '"&TEXT(DATEVALUE("1/10/2020"),"yyyy-mm-dd")&"'")
Notice that only the rows where the date is before 1/10/2020 are returned.
Example 2: Filter Rows After Some Date
We can use the following formula to filter for rows where the date is after 1/10/2020:
=QUERY(A1:C17,"select * where A > date '"&TEXT(DATEVALUE("1/10/2020"),"yyyy-mm-dd")&"'")
Notice that only the rows where the date is after 1/10/2020 are returned.
Example 3: Filter Rows Between Two Dates
We can use the following formula to filter for rows where the date is between 1/5/2020 and 1/15/2020:
=QUERY(A1:C17,"select * where A > date '"&TEXT(DATEVALUE("1/5/2020"),"yyyy-mm-dd")&"'and A < date '"&TEXT(DATEVALUE("1/15/2020"),"yyyy-mm-dd")&"'")
Notice that only the rows where the date is between 1/5/2020 and 1/15/2020 are returned.
The following tutorials explain how to perform other common operations with dates in Google Sheets: