Spreadsheets

QUERY with a date range in Google Sheets (where date between)

Filter by date range in a Google Sheets QUERY: the date literal syntax, building it from cells with TEXT, and why you get no results.

Editorial illustration for QUERY with a date range in Google Sheets (where date between)

QUERY has no BETWEEN keyword, so write a date range as two comparisons with date literals: =QUERY(A:E,"select A, B, E where A >= date '2026-04-01' and A <= date '2026-04-30'",1). The literal must be the word date, then a year-month-day value in single quotes, whatever way your sheet displays dates. Most "no results" problems come from breaking that rule.

What is the exact syntax?

Inside the query string, a date is written as date 'yyyy-mm-dd'. Four digits for the year, two for the month, two for the day, joined by hyphens, in single quotes, with the word date in front. Say your sheet has the date in A, merchant in B, category in C, account in D and amount in E:

=QUERY(A:E,"select A, B, C, E where A >= date '2026-04-01' and A <= date '2026-04-30' order by A",1)

Both end dates are included. The date order shown in your cells does not matter, since an Indian sheet showing 14/04/2026 stores a real date underneath. QUERY compares the underlying value.

How do I take the dates from cells?

Put the start in H1 and the end in H2, as real dates. Convert each to text in the required shape with TEXT and join it into the string:

=QUERY(A:E,"select A, B, C, E where A >= date '"&TEXT(H1,"yyyy-mm-dd")&"' and A <= date '"&TEXT(H2,"yyyy-mm-dd")&"' order by A",1)

This is where most mistakes happen, so build it in pieces: first test =TEXT(H1,"yyyy-mm-dd") on its own and confirm it shows 2026-04-01, then paste it into the longer formula.

Why do I get no results or an error?

SymptomLikely causeFix
Empty result, no errorDates in the sheet are text, not real datesTest with =ISNUMBER(A2); convert text dates with DATEVALUE or Data, Split text to columns
Unable to parse query stringMissing word date, or double quotes around the dateWrite date 'yyyy-mm-dd' with single quotes
Empty resultLiteral in day-month-year orderUse year, then month, then day
Missing the last dayThe column has times as well as datesUse A < date '2026-05-01' as the upper bound instead of <= the last day
Wrong rows includedHeader count wrongCheck the last argument; 1 for one header row

What about a whole financial year?

Use the first and last day of the year you want. For April 2025 to March 2026, as a hypothetical:

where A >= date '2025-04-01' and A <= date '2026-03-31'

To avoid retyping, keep the year start in a cell, say H3 holding 1-Apr-2025, and derive the end with EDATE: set H4 to =EDATE(H3,12)-1, which gives 31 March 2026. Then feed H3 and H4 into the TEXT pattern above.

Can I combine the date range with other conditions?

Yes. Add and between the clauses. For one category, with the name in H5:

=QUERY(A:E,"select A, B, E where C = '"&H5&"' and A >= date '"&TEXT(H1,"yyyy-mm-dd")&"' and A <= date '"&TEXT(H2,"yyyy-mm-dd")&"' order by A",1)

Add group by if you want totals instead of rows: replace the select with select C, sum(E), remove order by A, and finish the string with group by C.

A worked check

Say April has three rows dated 3, 14 and 29 April with amounts 1,200, 850 and 2,450. The April query returns all three, and a sum over them reads ₹4,500. A May query returns nothing. If your April query returns two rows, the missing one probably has a time on 30 April or text in the date cell.

Debugging checklist

  1. Use =ISNUMBER(A2) to check the date column
  2. Test the TEXT part alone
  3. Confirm single quotes around the date literal
  4. Check the header argument
  5. Use the less-than-next-month bound if times are present

If you are filtering by date all the time, SpendixAI's Report builder lets you filter and group transactions, then download a CSV, with no query syntax to remember.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports