Formula example
Google Sheets QUERY Date Range Formula
Use this page when you need a copy-paste QUERY formula that returns rows between two dates in Google Sheets. You can use fixed dates, start and end date cells, or a dynamic current-month range. The examples use a safer exclusive upper boundary so your query keeps working when the source column contains timestamps.
Copy-paste formula
=QUERY(A1:D100, "SELECT A, B, C, D WHERE A >= date '2026-01-01' AND A < date '2026-02-01'", 1)With the sample data, this returns the two January rows and excludes February rows because the upper boundary is before 2026-02-01.
Useful variations
=QUERY(A1:D100, "SELECT A, B, C, D WHERE A >= date '"&TEXT(F1,"yyyy-mm-dd")&"' AND A < date '"&TEXT(G1,"yyyy-mm-dd")&"'", 1)Use this when users choose start and end dates in spreadsheet cells.
=QUERY(A1:D100, "SELECT A, B, C, D WHERE A >= date '"&TEXT(EOMONTH(TODAY(),-1)+1,"yyyy-mm-dd")&"' AND A < date '"&TEXT(EOMONTH(TODAY(),0)+1,"yyyy-mm-dd")&"'", 1)Use this dynamic range for a report that updates during the current month.
=QUERY(A1:D100, "SELECT A, B, D WHERE B = 'East' AND A >= date '"&TEXT(F1,"yyyy-mm-dd")&"' AND A < date '"&TEXT(G1,"yyyy-mm-dd")&"'", 1)Join text, number, and date conditions inside WHERE with AND.
=QUERY(A1:D100, "SELECT A, B, D WHERE B contains 'Widget' AND A >= date '2026-01-01' AND A < date '2026-02-01'", 1)Use contains when the text match can appear inside a longer value.
=QUERY(A1:D100, "SELECT A, B, D WHERE A >= date '"&TEXT(TODAY(),"yyyy-mm-dd")&"' AND A < date '"&TEXT(TODAY()+1,"yyyy-mm-dd")&"'", 1)Use today through tomorrow when source dates may include time values.
Sample data
| Date | Region | Product | Amount |
|---|---|---|---|
| 2026-01-04 | East | Widget | 420 |
| 2026-01-12 | West | Widget | 310 |
| 2026-02-03 | East | Gadget | 275 |
| 2026-02-15 | East | Widget | 640 |
When to use this formula
- Use this when you need to return rows or values by date range.
- Use this when Stack Overflow-style QUERY date formulas fail because of date literals or cell references.
- Use the formula builder when your selected columns, date column, or criteria change often.
- Use QUERY date ranges when you want matching rows, not just a total.
- Use QUERY when you also need to select columns, order rows, or build a report view.
- Use FILTER instead when the formula should stay simpler and return the original columns.
- Use A >= the start date and A < the next day or next month when date-time values may exist in the source column.
Date range from start and end cells
Use TEXT around each date cell so the QUERY string receives date literals instead of raw cell references.
=QUERY(A1:D100, "SELECT A, B, C, D WHERE A >= date '"&TEXT(F1,"yyyy-mm-dd")&"' AND A < date '"&TEXT(G1,"yyyy-mm-dd")&"'", 1)F1 and G1 should contain real Google Sheets date values.
Fix invalid date literal from start and end cells
If QUERY reports Invalid date literal [44713], the start or end date cell was probably concatenated directly into the query string.
Convert each date cell with TEXT(date_cell,"yyyy-mm-dd") before joining it to the date keyword.
=QUERY(A1:D100, "SELECT A, B, C, D WHERE A >= date '"&TEXT(F1,"yyyy-mm-dd")&"' AND A < date '"&TEXT(G1,"yyyy-mm-dd")&"'", 1)This avoids serial-number date literals such as 44713.
Query the current month
Use EOMONTH to build a moving current-month window that starts on the first day of this month and stops before next month.
=QUERY(A1:D100, "SELECT A, B, C, D WHERE A >= date '"&TEXT(EOMONTH(TODAY(),-1)+1,"yyyy-mm-dd")&"' AND A < date '"&TEXT(EOMONTH(TODAY(),0)+1,"yyyy-mm-dd")&"'", 1)This updates automatically as TODAY changes.
Add another condition to the date range
Put the text or number condition in the same WHERE clause and join it to the date boundaries with AND.
=QUERY(A1:D100, "SELECT A, B, D WHERE B = 'East' AND A >= date '"&TEXT(F1,"yyyy-mm-dd")&"' AND A < date '"&TEXT(G1,"yyyy-mm-dd")&"'", 1)This returns East rows in the selected date range.
Timestamp-safe boundaries
Use A < date 'next-boundary' instead of <= the displayed end date when the source column may contain times.
A row on 2026-01-31 at 3:00 PM can be missed by a midnight-only end date, but it is included by A < date '2026-02-01'.
Returned rows from the sample data
| Date condition | Rows returned | Why |
|---|---|---|
| A >= date '2026-01-01' and A < date '2026-02-01' | 2026-01-04 East Widget 420 and 2026-01-12 West Widget 310. | Both dates are in January and before the February boundary. |
| A >= date '2026-02-01' and A < date '2026-03-01' | 2026-02-03 East Gadget 275 and 2026-02-15 East Widget 640. | Both dates are in February and before the March boundary. |
| B = 'East' plus the January range | 2026-01-04 East Widget 420 only. | The West January row is excluded by the extra Region condition. |
| A >= date from F1 and A < date from G1 | Matches the fixed-date result when F1 and G1 contain the same real dates. | TEXT converts the selected cells into QUERY date literals. |
Formula explanation
- QUERY date filters use date literals inside the query string.
- The upper boundary uses < the next month to avoid time-value edge cases.
- The cell-based version converts F1 and G1 with TEXT(F1,"yyyy-mm-dd") and TEXT(G1,"yyyy-mm-dd").
- The final 1 tells QUERY that the source range has one header row.
Common errors
- Do not put F1 directly inside date 'F1'.
- Do not concatenate F1 or G1 directly into QUERY or you may get an Invalid date literal such as 44713.
- Do not use display dates such as 1/1/2026 inside the query string.
- Do not use = date 'YYYY-MM-DD' when the source cells include timestamps; use a start/end range.
- Do not forget AND between the text condition and the date range.
- Do not mix A/B/C column letters with Col1/Col2 unless the source range requires Col notation.
- Do not write display dates such as 1/1/2026 inside QUERY. Locale settings can make them fail.
- QUERY date comparisons need date 'YYYY-MM-DD' inside the query string.
- The header row parameter, such as the final 1, affects labels and which row QUERY treats as data.
- If you change the source range, update the SELECT, WHERE, and ORDER BY column letters to match the new range.
- If you use cell references, wrap each date cell in TEXT(date_cell,"yyyy-mm-dd") inside the query string.
Build your own version
Use the deterministic builder for this pattern: Google Sheets QUERY Formula Builder.
Related formulas
FAQ
How do I query values by date range in Google Sheets?
Use a lower date boundary and an upper date boundary, such as A >= date '2026-01-01' AND A < date '2026-02-01'.
How do I use start and end date cells in QUERY?
Concatenate the cells with TEXT(F1,"yyyy-mm-dd") and TEXT(G1,"yyyy-mm-dd") so QUERY receives valid date literals.
How do I combine contains with a date range?
Put both conditions inside WHERE and join them with AND, such as B contains 'Widget' AND A >= date '2026-01-01'.
Why does QUERY not pick up my date range?
The usual causes are display dates, direct cell references inside date quotes, missing AND, or timestamp values that need a start/end range.
Should I use QUERY or FILTER for date ranges?
Use QUERY when you need selected columns, sorting, or a report view. Use FILTER when you want a simpler row filter with the original columns.