Spreadsheet Practice

How to filter between two dates with QUERY in Google Sheets

Google Sheets formula

=QUERY(A1:F13,"select A, B, F where A >= date '"&TEXT(I1,"yyyy-mm-dd")&"' and A <= date '"&TEXT(I2,"yyyy-mm-dd")&"'",1)

Build the dates into the query as yyyy-mm-dd text: =QUERY(A1:F13,"select A, B, F where A >= date '"&TEXT(I1,"yyyy-mm-dd")&"' and A <= date '"&TEXT(I2,"yyyy-mm-dd")&"'",1).

Result on the example
a 7-row list that spills from H4 in H4
Excel
See below

See it work

The formula is already in H4. Change any input and the result updates, or edit the formula to experiment.

How it works

QUERY only recognizes a date written as the word date followed by the date in single quotes, in yyyy-mm-dd form. TEXT(I1,"yyyy-mm-dd") turns the date in the cell into exactly that text, so the query reads where A >= date '2026-07-14' and A <= date '2026-08-04'. The and makes a range.

=QUERY(A1:F13,"select A, B, F where A >= date '"&TEXT(I1,"yyyy-mm-dd")&"' and A <= date '"&TEXT(I2,"yyyy-mm-dd")&"'",1)

Result{7 rows × 3 cols} "Date", "Rep", "Revenue", 46217, …

  1. A1:F1313 rows × 6 cols: "Date", "Rep", "Region", "Product", …

    A range: 13 rows × 6 columns.

  2. I17/14/2026

    The value in I1.

  3. TEXT(I1,"yyyy-mm-dd")"2026-07-14"

    TEXT: Formats a number or date as text, e.g. "mmm" or "$#,##0".

    • valueI17/14/2026
    • format_text"yyyy-mm-dd"
  4. "select A, B, F where A >= date '"&TEXT(I1,"yyyy-mm-dd")"select A, B, F where A >= date '2026-07-14"

    The & operator joins as text.

  5. "select A, B, F where A >= date '"&TEXT(I1,"yyyy-mm-dd")&"' and A <= date '""select A, B, F where A >= date '2026-07-14' and A <= date '"

    The & operator joins as text.

  6. I28/4/2026

    The value in I2.

  7. TEXT(I2,"yyyy-mm-dd")"2026-08-04"

    TEXT: Formats a number or date as text, e.g. "mmm" or "$#,##0".

    • valueI28/4/2026
    • format_text"yyyy-mm-dd"
  8. "select A, B, F where A >= date '"&TEXT(I1,"yyyy-mm-dd")&"' and A <= date '"&TEXT(I2,"yyyy-mm-dd")"select A, B, F where A >= date '2026-07-14' and A <= date '2026-08-04"

    The & operator joins as text.

  9. "select A, B, F where A >= date '"&TEXT(I1,"yyyy-mm-dd")&"' and A <= date '"&TEXT(I2,"yyyy-mm-dd")&"'""select A, B, F where A >= date '2026-07-14' and A <= date '2026-08-04'"

    The & operator joins as text.

  10. QUERY(A1:F13,"select A, B, F where A >= date '"&TEXT(I1,"yyyy-mm-dd")&"' and A <= date '"&TEXT(I2,"yyyy-mm-dd")&"'",1){7 rows × 3 cols} "Date", "Rep", "Revenue", 46217, …

    QUERY: Runs a SQL-like query: select, where, group by, order by, limit, label.

    • dataA1:F1313 rows × 6 cols: "Date", "Rep", "Region", "Product", …
    • query"select A, B, F where A >= date '"&TEXT(I1,"yyyy-mm-dd")&"' and A <= date '"&TEXT(I2,"yyyy-mm-dd")&"'""select A, B, F where A >= date '2026-07-14' and A <= date '2026-08-04'"
    • headers1

In Excel

Excel has no QUERY function. Use FILTER with two date tests: =FILTER(A2:F13,(A2:A13>=I1)*(A2:A13<=I2)).

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=QUERY(A1:F13,"select A, B, F where A >= date '2026-07-14' and A <= date '2026-08-04'",1)spills 7 × 3Typed dates work too, for a one-off report.
=FILTER({A2:A13,B2:B13,F2:F13},A2:A13>=I1,A2:A13<=I2)spills 6 × 3Different resultFILTER compares real dates with no text conversion. No header row. No header row.

Common mistakes

  • where A >= I1 or where A >= '7/14/2026' doesn't work: QUERY needs the date keyword and the yyyy-mm-dd form.
  • If the column holds dates with times, use datetime '2026-08-04 23:59:59', or compare with < the next day.
  • Dates stored as text in the source column are ignored, because QUERY gives each column one type.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Filter between two dates with QUERY

In H4, use QUERY to return the Date, Rep and Revenue of the orders dated from I1 to I2, both days included.

Questions

How do I get the last 30 days?

Use TODAY in the TEXT: "where A >= date '"&TEXT(TODAY()-30,"yyyy-mm-dd")&"'".

How do I filter one month?

Use month(), which counts from 0: where month(A) = 6 is July. Add and year(A) = 2026 for a single year.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.