Google Sheets Query: How to Filter by Date Range


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.

Additional Resources

The following tutorials explain how to perform other common operations with dates in Google Sheets:

How to AutoFill Dates in Google Sheets
How to Add & Subtract Days in Google Sheets
How to Calculate the Difference Between Two Dates in Google Sheets

Featured Posts

One Reply to “Google Sheets Query: How to Filter by Date Range”

  1. Instead of using TEXT(DATEVALUE(“1/1/2020″),”yyyy-mm-dd”) for a hardcoded date, it would be much easier to use “2020-01-01” and skip the conversions!

Leave a Reply

Your email address will not be published. Required fields are marked *