Date Logic in QUERY Function in Google Sheets

When you start learning the QUERY function, applying date logic in QUERY is often where you may stumble.

The QUERY function in Google Sheets supports powerful date-based filtering, grouping, and calculations. However, date logic in QUERY behaves differently from regular spreadsheet formulas, which is a common source of confusion for many users.

This hub explains how date logic works in the Google Sheets QUERY function, the common patterns you should know, and links to detailed tutorials for each specific use case.

Table of Contents

How Dates Work in Google Sheets QUERY

In Google Sheets, dates are stored as date serial numbers, not text values. The QUERY function evaluates dates numerically, which means:

Dates must be in a recognized date format
Text-formatted dates may not work as expected
Date conditions often require explicit date construction

To understand this behavior in depth, see:
How to Use Date Values (Date Serial Numbers) in Google Sheets Query

Using Date Criteria in the WHERE Clause

Most date criteria in Google Sheets QUERY are implemented using the WHERE clause. You can filter rows based on:

Exact dates
Date ranges
Relative dates (today, current month, previous month, etc.)

Example pattern:

=QUERY(A1:C, "select * where A >= date '2026-02-01'", 1)

This returns rows where the date in column A is greater than or equal to February 1, 2026.

The default date format (for example, when you enter =TODAY() in a new sheet) may be dd/mm/yyyy, mm/dd/yyyy, or yyyy-mm-dd.
In QUERY date literals, you should always use the format yyyy-mm-dd.

A full breakdown of supported date conditions—such as using dates as cell references and applying comparison operators—is covered here:
How to Use Date Criteria in Query Function in Google Sheets

Working with Months in QUERY

Month-based filtering is one of the most common reporting requirements.

Common scenarios include:

Filter rows for a specific month
Query by month and year together
Convert month numbers to month names
Query data by month name instead of date values

Current, Previous, and Relative Months

QUERY does not automatically understand terms like “current month” or “previous month” unless you explicitly define date boundaries.

Typical use cases include:

Extracting data from the current month
Filtering rows from the previous month
Summing values for the current month only

In QUERY, month numbers range from 0 to 11, not 1 to 12.

Example:

=QUERY(A1:C, "select * where month(A)=0", 1)

This filters rows from January, regardless of the year.

When working with the previous month, using month()-1 may seem logical, but it breaks when the current month is January. Refer to the guides below for correct handling of current, previous, and relative months in QUERY.

Detailed guides:

Adding or Subtracting Days in QUERY

Sometimes date logic in QUERY requires dynamically adjusting dates, such as:

Adding days to a date
Subtracting days to create rolling windows
Comparing shifted dates

QUERY does not support direct date math. Since it evaluates dates numerically, you must handle this carefully.

One approach is to format the date column as numbers and then use a QUERY formula such as:

=QUERY(A1:C, "select A+30", 1)

Afterward, format the result as dates (Format > Number > Date).

Note that the column header may appear as something like sum(30()). You can rename this header using the LABEL clause in the QUERY statement.

If you prefer not to format the source date column as numbers, you can convert dates to numbers within the QUERY data range itself.

See the full explanation here:
Add or Subtract Days from Dates in Google Sheets QUERY

Using DateTime Values in QUERY

When your data includes both date and time, additional care is required.

QUERY can handle DateTime values, but:

Time portions affect comparisons
Midnight boundaries can exclude rows unexpectedly

Example:

=QUERY(A1:C, "select * where A > datetime '2026-01-31 00:00:00'", 1)

Here, a DateTime value is used in the WHERE clause. The same syntax can also be used in GROUP BY and FORMAT clauses.

Learn how to handle DateTime values correctly here:
How to Use DateTime in a QUERY in Google Sheets

Querying Non-Calendar Periods (Quarters & Custom Ranges)

Not all reporting follows calendar months or standard quarters.

Google Sheets QUERY can be adapted to:

Handle non-calendar quarters
Work with custom financial periods

To achieve this cleanly, you typically need to create a helper column. This keeps QUERY formulas simple and reliable.

Guide:
How to Query Non-Calendar Quarters in Google Sheets

Best Practices for Date Logic in QUERY

When working with Google Sheets QUERY dates, keep the following best practices in mind:

Ensure date columns contain true date values
Avoid text-formatted dates
Use explicit date boundaries for month-based filtering
Be cautious with DateTime comparisons
Test edge cases (month start/end, leap years)

Conclusion

Understanding Date Logic in QUERY is essential for accurate reporting and analysis in Google Sheets.

Once you understand:

how QUERY evaluates dates,
how to build reliable date conditions, and
how to handle months, years, and time values,

you can confidently apply date filters to almost any dataset.

Use this hub as a central reference and explore the linked tutorials for detailed, real-world examples of date logic in the Google Sheets QUERY function.