Google Sheets Query Date Comparison: Master Date Filters
Learn how to compare dates with Google Sheets QUERY, including dynamic date filters, text-date normalization, and practical templates for daily, weekly, and monthly data. Practical guidance for students, professionals, and small businesses.
Direct answer: In Google Sheets, you compare dates inside a QUERY by using a dynamic date literal and a proper date column. Build the literal with TODAY() minus a number of days and concatenate in the query. Normalize text dates with DATEVALUE to avoid misreads. According to How To Sheets, this approach scales from daily to monthly filters.
Understanding date types in Google Sheets
Dates in Sheets can be true date serials or plain text. Treat both consistently; otherwise comparisons behave inconsistently. According to How To Sheets, the safest approach is to normalize all date values to true serial dates before performing comparisons. Use DATE, DATEVALUE, or TO_DATE to standardize formats as you build queries and formulas.
=IF(A2 < TODAY(), "Past", "Future")- This simple example compares a date in A2 to today, returning "Past" or "Future". If A2 contains text like "2026-04-01", convert it with DATEVALUE(A2) or DATEVALUE(TEXT(A2,"yyyy-mm-dd")).
Using QUERY for date comparisons
The Google Sheets QUERY language supports filtering by dates using a date literal. Build the query string dynamically and concatenate the computed date. Example filters the last 30 days from a two-column range:
=QUERY(Sheet1!A:B, "select A, B where A > date '" & TEXT(TODAY()-30, "yyyy-MM-dd") & "'", 1)This pattern can be adjusted to other ranges or to include time-bounded ranges, for instance using TODAY() in the left side and a fixed end date on the right. Another variant filters by a date range:
=QUERY(Sheet1!A:C, "select A, C where A >= date '" & TEXT(TODAY(), "yyyy-MM-dd") & "' and A <= date '" & TEXT(TODAY()-7, "yyyy-MM-dd") & "'", 1)Normalizing date values when dates are stored as text
When dates arrive as text, they do not compare correctly with real date numbers. Normalize using DATEVALUE or VALUE inside an ARRAYFORMULA to keep the entire column reliable for queries. This approach minimizes errors when dates come from imports or manual entry.
=ARRAYFORMULA(IF(ISNUMBER(A2:A), A2:A, DATEVALUE(A2:A)))If your locale uses non-ISO formats, you may need to pre-format or convert to ISO 8601 before using DATEVALUE.
Working with date-time values and time zones
Date-time stamps include a time portion that can break pure date comparisons. To compare by date only, strip the time part with INT or use TEXT to format the date portion. This ensures that dates like 2026-04-01 12:34:56 compare exactly to other dates.
=ARRAYFORMULA(TEXT(INT(A2:A), "yyyy-MM-dd"))Another option is to ignore time by converting to date serials before feeding into QUERY:
=QUERY(Data!A:C, "select * where A >= date '" & TEXT(TODAY()-30, "yyyy-MM-dd") & "'", 1)Practical scenarios and templates
Real-world datasets often include orders, events, or logs with date fields. Here are templates you can adapt:
-- Last 14 days
=QUERY(Orders!A:C, "select * where A >= date '"&TEXT(TODAY()-14,"yyyy-MM-dd")&"'", 1)-- Filter by a report date in a cell (J1)
=QUERY(Orders!A:C, "select * where A > date '"&TEXT($J$1,"yyyy-MM-dd")&"'", 1)These templates demonstrate how to drive date-based reporting with dynamic and fixed dates, enabling dashboards that refresh automatically.
Common pitfalls and performance tips
- Inconsistent date formats or locales can lead to wrong results. Normalize dates early and test with known values.
- When using QUERY, prefer explicit date literals (yyyy-MM-dd). The parser can misinterpret ambiguous strings.
- If you export data, preserve date types rather than text for reliability. Use a helper column for type-checks.
=ARRAYFORMULA(IFERROR(DATEVALUE(A2:A), "Invalid date"))By factoring date normalization into a separate column, you reduce query complexity and improve performance for large sheets.
Advanced tips for robust date queries
Additional tips to handle edge cases: time zones, leap years, and daylight saving adjustments may affect date logic. Use DATE, DATEVALUE, and TEXT to unify across locales. For reproducible results, pin the timezone in the Sheets settings. When sharing sheets, remind collaborators that date formats may vary across locales; set a consistent convention.
=ARRAYFORMULA(IFERROR(DATEVALUE(A2:A), "Invalid date"))In summary, maintain consistent data types, use explicit date literals, and leverage dynamic functions like TODAY() for auto-updating dashboards.
Steps
Estimated time: 15-25 minutes
- 1
Open data and identify date column
Open the dataset you will query and identify the column that contains date values. Confirm whether dates are true date serials or text that must be converted. This sets the foundation for reliable date comparisons.
Tip: Mark the header row and keep it consistent across all queries. - 2
Normalize date values
If dates are text, create a helper column that converts them to date serials using DATEVALUE or VALUE. This prevents subtle mismatches when querying by date.
Tip: Test with a few known dates to ensure the conversion works across formats. - 3
Write a dynamic QUERY for last days
Construct a query that uses a dynamic date like TODAY()-N to filter records. Build the date literal by concatenating the date string with the query, ensuring proper escaping.
Tip: Use yyyy-MM-dd format for the date literal inside the query. - 4
Test with different ranges
Experiment with different ranges (last 7, 30, 90 days) to validate behavior and confirm results mirror expectations.
Tip: Use a helper cell for the count of days to keep the query flexible. - 5
Validate data types and share
Check that downstream users see consistent date types and that the shared sheet uses a standard locale.
Tip: Document the date conventions in a sheet note. - 6
Document templates for reuse
Capture your most common date-query patterns as templates for future dashboards and reports.
Tip: Include comments in formulas to aid future maintainers.
Prerequisites
Required
- Google Sheets (web or app)Required
- Basic knowledge of formulas (IF, DATEVALUE, TEXT)Required
- Required
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy formula or snippet | Ctrl+C |
| PasteInsert copied content | Ctrl+V |
| Find in sheetLocate dates or headers | Ctrl+F |
| Toggle boldEmphasize formulas in cell editor | Ctrl+B |
FAQ
What is the best way to compare dates in Google Sheets using QUERY?
Use a dynamic date literal in the QUERY string and ensure the date column is a true date serial. Normalize text dates first if needed. This approach works for daily, weekly, and monthly filters and scales with data size.
Use a dynamic date in your QUERY and make sure dates are real date values.
How do I filter for dates within the last 7 days?
Calculate a date 7 days ago with TODAY()-7 and filter with a date literal in the QUERY. For example, select records where date column is greater than or equal to today minus seven days.
Filter by date range using TODAY()-7 in the QUERY.
Can I compare date-time values, not just dates?
Yes. Strip the time portion with INT or TEXT to compare only the date part, then apply the QUERY filter. This avoids mismatches caused by differing time stamps.
You can compare just the date parts by removing the time portion first.
Why might DATEVALUE return an error in a query?
DATEVALUE may fail on non-date text or regional formats. Normalize inputs first, or use a helper column to convert text to dates before applying the QUERY.
DATEVALUE can fail if the text isn’t a recognizable date, so normalize first.
How do locale and timezone affect date comparisons?
Locale and timezone settings can shift the interpretation of dates. Use explicit formats (yyyy-MM-dd) and, if needed, adjust the sheet’s locale settings so date parsing is consistent.
Locale matters for date parsing; keep formats explicit and consistent.
The Essentials
- Normalize dates before querying
- Use explicit date literals (yyyy-MM-dd)
- Leverage TODAY() for dynamic filters
- Convert text dates with DATEVALUE when needed
- Test with sample datasets to confirm behavior