Google Sheets QUERY Function: Filter by Date Range Fast

To filter a dataset by a date range using the Google Sheets QUERY function, you must explicitly declare the data as a date and format it as YYYY-MM-DD. Assuming your source data is A1:D, your date column is B, and your dynamic start/end dates are in cells F1 and G1, use this exact formula:

Excel

=QUERY(A1:D, "SELECT * WHERE B >= date '" & TEXT(F1, "yyyy-mm-dd") & "' AND B <= date '" & TEXT(G1, "yyyy-mm-dd") & "'", 1)

The Syntax Breakdown

• QUERY(A1:D, ... , 1): The core function. A1:D is your raw data range, and the 1 at the very end tells Google Sheets that the first row of your range contains headers.
• "SELECT *": Instructs the query to output all columns for the rows that successfully match your date criteria.
• WHERE B >= ... AND B <= ...: The logical condition filtering for rows where the date in Column B falls between (inclusive) your defined start and end points.
• date '...': The mandatory Google Visualization API syntax. Without the word date and the single quotes enclosing the date string, the query engine treats dates as either basic math (division) or plain text, which will instantly break the formula.
• " & TEXT(F1, "yyyy-mm-dd") & ": This breaks out of the query string to reference a live cell (F1). The TEXT function is critical here: it forces whatever date you typed into F1 into the strict YYYY-MM-DD text format required by the API.

Real-World Example: Auditing Q1 Software Subscription Renewals

You are an Operations Manager pulling a report on software subscriptions renewing specifically in February 2024.

Your start date (02/01/2024) is typed into cell F1. Your end date (02/29/2024) is typed into cell G1.

Here is your raw data table in A1:D5:

The Application:

You paste the dynamic formula into cell F4 (or any blank area where the data can spill downward):

Excel

=QUERY(A1:D5, "SELECT * WHERE B >= date '" & TEXT(F1, "yyyy-mm-dd") & "' AND B <= date '" & TEXT(G1, "yyyy-mm-dd") & "'", 1)

The Output:

The formula successfully skips January and March, returning only the February renewals alongside the original headers.

Common Errors & How to Fix Them

• #VALUE! (Unable to parse query string for Function QUERY parameter 2): You concatenated a raw Google Sheets cell directly into the query, which passes a raw serial number (e.g., 45320) instead of a recognized date. Fix: Wrap your cell references in the TEXT() function exactly as shown above: TEXT(Cell, "yyyy-mm-dd").
• Empty Output (#N/A Query completed with an empty output): The dates in your source column are formatted as text strings, so the < or > math fails to recognize them as chronological values. Fix: Highlight your source date column, click Format > Number > Date to standardize the underlying data type.
• #VALUE! (NO_COLUMN: B): You wrapped your data range in curly brackets (e.g., {A1:D5}) to combine sheets, but kept using column letters in your string. Fix: When querying arrays enclosed in { }, you must replace column letters with Col numbers (e.g., change WHERE B >= to WHERE Col2 >=).