How to filter by date with QUERY in Google Sheets
QUERY compares dates only when the date is written as date 'yyyy-mm-dd'. This guide shows the working formula, the three ways it usually fails, and the patterns for a date from a cell, a range of dates, today, a month or a quarter, and date-time columns.
To filter by date in a Google Sheets QUERY, write the date as date ‘yyyy-mm-dd’ inside the query string:
=QUERY(B2:D12, "select * where B > date '2023-05-01'", 1)
The word date, the single quotes and the year-month-day order are all required. A date typed as 05/01/2023, with or without quotes, either returns an error or returns nothing.
| You need | Query string |
|---|---|
| Rows after a date | "select * where B > date '2023-05-01'" |
| Rows between two dates | "select * where B >= date '2023-05-01' and B <= date '2023-08-01'" |
| A date taken from a cell | "select * where B > date '" & TEXT(H10, "yyyy-mm-dd") & "'" |
| Rows before today | "select * where B < date '" & TEXT(TODAY(), "yyyy-mm-dd") & "'" |
| The last 30 days | "select * where B >= date '" & TEXT(TODAY()-30, "yyyy-mm-dd") & "'" |
| One month (June 2023) | "select * where year(B) = 2023 and month(B) = 5" |
The rest of this guide explains each pattern, the errors you get when the date is written in another way, and what to do with date-time columns. For the function itself, see the guide to the QUERY function.
The working formula: the date keyword and yyyy-mm-dd
The working formula: the date keyword and yyyy-mm-dd
QUERY takes three arguments:
=QUERY(data, query, [headers])
- data: the range to query.
- query: the query string, in quotes.
- headers (optional): the number of header rows at the top of the range.
The sample table holds a date, a sales rep and a sales amount in B2:D12, with the header in row 2. To return the sales made after May 1, 2023:
=QUERY(B2:D12, "select * where B > date '2023-05-01'", 1)
- B2:D12: the data, header row included.
- select *: return all columns.
- where B > date ‘2023-05-01’: keep the rows where the date in column B is later than May 1, 2023.
- 1: the range has one header row, and it is carried over to the result.
The formula returns six rows, from May 25 to October 20. The dates in the sheet can be displayed in any format, 5/25/2023 or 25 May 2023: the format in the query string does not depend on it and is always yyyy-mm-dd.
Three ways the date filter fails
Three ways the date filter fails
The screenshots in this section come from a similar table, and the query asks for the same thing: sales after May 1, 2023.
A plain date returns #VALUE!
=QUERY(B2:D12, "SELECT * WHERE B > 05/01/2023", 0)
QUERY cannot read 05/01/2023 as a value: it stops at the first slash and reports that it is unable to parse the query string.
A date in quotes returns nothing
=QUERY(B2:D12, "SELECT * WHERE B > '05/01/2023'", 0)
Anything in single quotes is a text string. A date column is never greater than a piece of text, so no row passes and the result is #N/A with the message “Query completed with an empty output”. This one is easy to miss, because the query is valid: with a header row in the result you get the header and no rows, without any error.
The date keyword with the wrong format returns #VALUE!
=QUERY(B3:D12, "SELECT * WHERE B > date '05/01/2023'", 0)
The keyword is there, but the date is not in year-month-day order. The error text says so directly: date literals should be of the form yyyy-MM-dd.
OWOX Data Marts
See your first report built in real time. 15 minutes.
- Connect your data warehouse
- Pick your metrics
- Get a live Google Sheets report
In the time it takes to write a ticket. Then imagine never writing that ticket again.
Book a DemoWe'll use your actual use case
Use a date from a cell
A date typed into the query has to be edited by hand. To let the reader of the report change it, keep the date in a cell and build the literal with TEXT, which turns the date into a yyyy-mm-dd string:
=QUERY(B2:E12, "SELECT * WHERE B > date '" & TEXT(H10, "yyyy-mm-dd") & "'", 0)
- B2:E12: the data: date, sales rep, region and sales amount.
- “SELECT * WHERE B > date ’”: the first part of the query, up to the opening single quote.
- TEXT(H10, “yyyy-mm-dd”): the date from cell H10 as text, for example 2023-06-01.
- “’”: the closing single quote.
The three parts are joined with ampersands (&). Change the date in H10 and the result follows.
The formula in the screenshot wraps the cell in DATEVALUE as well: TEXT(DATEVALUE(H10), "yyyy-mm-dd"). With a real date in H10 both versions return the same rows. DATEVALUE earns its place when the cell holds the date as text.
Passing the cell without TEXT does not work. "select * where B > " & H10 puts the serial number of the date into the query, and comparing a date column with a number returns no rows.
Filter between two dates
Join two conditions with and. To return the sales from May 1 to August 1, 2023, both dates included:
=QUERY(B2:E12, "select * where B >= date '2023-05-01' and B <= date '2023-08-01'", 1)
Use > and < instead of >= and <= to leave the two boundary dates out.
The screenshot builds the same two literals with TEXT and DATEVALUE from dates typed as “05/01/2023” and “08/01/2023”. The result is identical; the short form above is easier to read. With the two dates in cells, use TEXT(H10, "yyyy-mm-dd") and TEXT(H11, "yyyy-mm-dd") in their place.
Filter by today’s date
TODAY() returns the current date, so a filter built on it moves forward every day without anyone editing the formula. To return the rows dated after today:
=QUERY(B2:E12, "SELECT * WHERE B > date '" & TEXT(TODAY(), "yyyy-mm-dd") & "'", 0)
The screenshot again wraps TODAY() in DATEVALUE; it is not needed, because TODAY() already returns a date.
The same pattern covers the usual reporting periods:
- Up to yesterday:
where B < date '" & TEXT(TODAY(), "yyyy-mm-dd") & "' - The last 30 days:
where B >= date '" & TEXT(TODAY()-30, "yyyy-mm-dd") & "' - This month so far:
where B >= date '" & TEXT(EOMONTH(TODAY(), -1)+1, "yyyy-mm-dd") & "'
More on these two functions in the guides to NOW and TODAY and MONTH and EOMONTH.
Filter by month, year or quarter
Filter by month, year or quarter
The query language has its own date functions, and they save you from writing two boundary dates: year(), month(), quarter(), day() and dayOfWeek().
There is one trap: month() counts from 0. January is 0, May is 4 and December is 11. This formula looks like a filter for May and returns the sales of June:
=QUERY(B2:D12, "select * where year(B) = 2023 and month(B) = 5", 1)
For May, write month(B) = 4. quarter() counts from 1, so where quarter(B) = 2 returns April, May and June.
Date-time columns
When the column holds a date and a time, a date literal stands for midnight of that day. That has two consequences:
where B > date '2023-05-01'also returns the rows of May 1 itself, because 12:00 on May 1 is later than midnight.where B = date '2023-05-01'returns nothing, because no timestamp is exactly midnight.
Two ways to handle it:
- Compare the day only:
where toDate(B) = date '2023-05-01' - Compare with a moment in time:
where B > datetime '2023-05-01 13:00:00'
Why the query returns nothing or the wrong rows
Why the query returns nothing or the wrong rows
⚠️ Error: #VALUE! with “Unable to parse query string”.
✅ Solution: The date is written without the date keyword or in the wrong order. Write it as date ‘yyyy-mm-dd’. When the literal is built from a cell, check that the single quotes are there on both sides of the TEXT part.
⚠️ Error: #N/A with “Query completed with an empty output”, or a header with no rows.
✅ Solution: The date is compared with a quoted string or with a number. Add the date keyword and build the value with TEXT(cell, “yyyy-mm-dd”).
⚠️ Error: Some rows are missing, although their dates match.
✅ Solution: Those dates are stored as text. QUERY assigns one type to each column, the type of most of its cells, and treats the other cells as empty. A text date is usually left-aligned and does not change when you change the number format. Convert the column to real dates; the guide to DATE functions covers DATEVALUE.
⚠️ Error: The wrong month comes back.
✅ Solution: month() counts from 0. Subtract 1 from the month number.
⚠️ Error: Curly quotes in a copied formula.
✅ Solution: A formula pasted from a document or a chat can carry typographic quotes (‘ ’ or “ ”) instead of straight ones. Retype the quotes in the formula bar.
Related Google Sheets functions
Related Google Sheets functions
Functions that are often used next to a date filter in QUERY, each with its own guide:
- FILTER: Returns the rows that meet a condition; for a simple date filter it needs no query string.
- QUERY with CONCATENATE: Builds query strings from parts, the same technique as the date from a cell.
- DATE: A set of functions for managing dates, including DATE for creating valid dates, EDATE for adjusting by months, DATEVALUE for converting text to dates and DATEDIF for calculating date differences.
- YEAR/YEARFRAC: Extracts the year from a date or returns the fraction of a year between two dates.
- MONTH/EOMONTH: Extracts the month from a date or returns the last day of a month, handy for monthly reports.
- WEEKDAY/WEEKNUM: Returns the day of the week or the week number of the year for a date, useful for weekly reporting.
- UNIQUE: Returns the distinct values of a range.
- Pivot tables: Summarize the filtered rows by month, rep or region.
The date filter is only as good as the dates in the tab
The date filter is only as good as the dates in the tab
Every pattern above assumes that column B holds real dates. In a report that is fed by exports this is the part that breaks: one file writes 05/01/2023, the next one 2023-05-01 as text, a third one adds a time. QUERY does not complain. It treats the cells it cannot read as empty, and the report quietly shows fewer rows than there are.
The OWOX Data Marts extension for Google Sheets covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.
What changes for the person who maintains the sheet:
- Refresh replaces re-export. Refresh current report in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
- The source is written on the sheet. The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
- No SQL and no warehouse access needed. The analyst decides which data marts and columns are available. You choose from that list.
The extension does not write or change the analyst’s SQL. What the sheet receives is the result of the same query on every refresh, not a new export with a new date format, so a QUERY date filter written once keeps reading the same column. The extension comes with the OWOX Cloud plans of OWOX Data Marts; spreadsheet reporting describes both ways data reaches a sheet.
Turn your data into decisions.
Governed data marts give you the clean foundation ML needs to actually work.
- No AI hallucinations
- Analyst-governed definitions
- Every number traces to SQL
Frequently Asked Questions
How do I filter data between two specific dates using the QUERY function?
Join two conditions with and, and write each date with the date keyword in yyyy-mm-dd format. For example: =QUERY(A1:C, "select * where A >= date '2023-01-01' and A <= date '2023-12-31'", 1) returns the rows dated within 2023, both boundary dates included.
What common issues arise with date handling in the QUERY function?
A date typed without the date keyword returns #VALUE!, and a date in quotes without the keyword returns no rows. Dates stored as text in the source column are skipped, month() counts from 0 so May is 4, and in a date-time column a date literal means midnight of that day.
Can I use today’s date as a filter in my QUERY?
Yes. TODAY() cannot be written inside the query string, so build the date literal outside it: "select * where A < date '" & TEXT(TODAY(), "yyyy-mm-dd") & "'". The filter then moves forward every day without editing the formula.
How can I ensure my dates are recognized by the QUERY function?
Write the date in the query as date 'yyyy-mm-dd': the word date, single quotes and year-month-day order. The column itself must hold real dates, not text; a text date is usually left-aligned and does not change when you change the number format.
Why is it crucial to use date filtering in the QUERY function?
Date filtering in the QUERY function is crucial for narrowing down data to specific timeframes, making it easier to analyze trends, track performance, and generate insights. It helps you focus on relevant data while avoiding clutter from irrelevant time periods.
What is the QUERY function in Google Sheets?
The QUERY function in Google Sheets allows you to retrieve specific data from a dataset by using SQL-like queries. It enables filtering, sorting, and performing calculations on data, making it a powerful tool for analyzing and managing large sets of information efficiently.
