Formula example
Google Sheets QUERY Date Range Formula
Direct answer: use A >= date '2026-01-01' and A < date '2026-02-01' when QUERY should return January rows. You can use fixed dates, start and end date cells, or a dynamic current-month range; the exclusive upper boundary 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(F1,"yyyy-mm-dd")&"' AND A < date '"&TEXT(G1+1,"yyyy-mm-dd")&"'", 1)Use this when G1 is the last visible date to include, such as 2026-01-31.
=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.
=FILTER(A2:D100, A2:A100>=F1, A2:A100<G1+1)Use FILTER when you want the original columns and do not need SELECT, ORDER BY, or FORMAT.
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.
Inclusive end date from a cell
If G1 is the last date users expect to include, add one day to G1 and still use the < operator.
This pattern includes all rows on the visible end date, even when the source Date column contains times.
=QUERY(A1:D100, "SELECT A, B, C, D WHERE A >= date '"&TEXT(F1,"yyyy-mm-dd")&"' AND A < date '"&TEXT(G1+1,"yyyy-mm-dd")&"'", 1)If F1 is 2026-01-01 and G1 is 2026-01-31, this queries January rows through the next-day boundary.
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'.
FILTER alternative for a date range
Use FILTER instead of QUERY when you only need matching rows in the original column order.
Keep the same timestamp-safe boundary idea: use >= the start date and < the day after the visible end date.
=FILTER(A2:D100, A2:A100>=F1, A2:A100<G1+1)F1 is the start date and G1 is the last visible date to include.
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. |
| A >= date from F1 and A < date from G1+1 | Includes the last visible end date entered in G1. | Useful when users enter 2026-01-31 instead of the next boundary 2026-02-01. |
QUERY or FILTER date range choice
| Need | Use | Example pattern |
|---|---|---|
| Selected columns, sorting, or a report view | QUERY | SELECT A, B, D WHERE A >= date '2026-01-01' AND A < date '2026-02-01' |
| Original rows with simpler syntax | FILTER | =FILTER(A2:D100, A2:A100>=F1, A2:A100<G1+1) |
| Date cells inside a QUERY string | QUERY with TEXT | date '"&TEXT(F1,"yyyy-mm-dd")&"' |
| Timestamp-safe end date | Either QUERY or FILTER | Use < next day or < next month instead of <= end date. |
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.
- 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 formula 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 include the end date in a QUERY date range?
Use A < date from the day after the visible end date, such as TEXT(G1+1,"yyyy-mm-dd"). This includes rows on G1 even when the source dates have time values.
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.