Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to use QUERY with date comparisons in Excel and Google Sheets
Topic:QUERY (Google Sheets SQL)Heads up: Excel and Google Sheets do this differently.
Excel
=FILTER(A2:C10,(A2:A10>=DATE(2024,1,10))*(A2:A10<=DATE(2024,1,25)))Google Sheets
=QUERY(A1:C10,"SELECT A, B, C WHERE A >= date '2024-01-10' AND A <= date '2024-01-25'",1)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Ensure your date column (A) contains real date values, not text strings that look like dates — click a cell and check the formula bar format.
- 2To filter for dates on or after a specific date, use: =QUERY(A1:C10,"SELECT A, B, C WHERE A >= date '2024-01-10'",1).
- 3To filter a date range, chain two conditions with AND: "SELECT A, B, C WHERE A >= date '2024-01-10' AND A <= date '2024-01-25'".
- 4To reference a cell for the date value instead of hardcoding it, use TEXT concatenation: "SELECT A, B, C WHERE A >= date '"&TEXT(E2,"yyyy-mm-dd")&"'".
- 5Use QUERY's toDate() or the date functions year(), month(), day() to extract date parts: "SELECT year(A), month(A), SUM(C) GROUP BY year(A), month(A)" rolls data up by year-month.
Tips
- The QUERY date literal must always be in ISO format: date 'yyyy-mm-dd'. If you write date '01/10/2024' or any other format, QUERY will return an error or wrong results regardless of your locale.
- If your dates are stored as text strings (LEFT(A2,10) shows '2024-01-10' as text), QUERY cannot compare them as dates. Convert the column to real dates first using DATEVALUE() or by reformatting.
- For 'current month' filtering, use TODAY() with TEXT: "WHERE A >= date '"&TEXT(EOMONTH(TODAY(),-1)+1,"yyyy-mm-dd")&"'" gives you the first day of the current month dynamically.
Need the Google Sheets version instead? Open Sheets variant in workspace →
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Why does my QUERY date filter return all rows or no rows?
The most likely cause is that your date column contains text values rather than true date serial numbers. Select a cell in the column, look at the format, and run =ISNUMBER(A2) — if it returns FALSE the value is text, not a real date.
Can I filter by a specific day of the week?
Yes, using QUERY's dayOfWeek() function: WHERE dayOfWeek(A) = 2 filters for Mondays (1=Sunday through 7=Saturday in the Google Visualization API).
How do I filter dates in the last 30 days dynamically?
Use TODAY() with TEXT: "WHERE A >= date '"&TEXT(TODAY()-30,"yyyy-mm-dd")&"'". This always evaluates to the current date minus 30 days when the sheet recalculates.
More on QUERY (Google Sheets SQL)
See all →How-to
How to use QUERY to filter data
Use Google Sheets QUERY with a WHERE clause to filter rows dynamically — Excel users can use FILTER as the equivalent.
Error fixFix QUERY error
QUERY parse error in Google Sheets means the SQL-like query string has a syntax mistake. Find the exact error and fix it here.
How-toHow to use QUERY to sum by group
Use Google Sheets QUERY with GROUP BY and SUM to aggregate data by category — use SUMIF for the Excel equivalent.
How-toHow to use QUERY to group by multiple columns
Use QUERY GROUP BY with multiple columns in Google Sheets to create multi-level summaries. Excel alternative uses SUMIFS with multiple criteria ranges.
How-toHow to use QUERY with WHERE, AND, OR
Filter QUERY results in Google Sheets using WHERE with AND and OR conditions. Excel users can replicate this with FILTER using * for AND and + for OR.
How-toHow to create a running total with QUERY
Build a running total in Google Sheets by combining QUERY for ordering with ARRAYFORMULA and SUMIF. Excel alternative uses a simple SUMIF with a locked anchor row.