Query Google Sheets timestamps between inclusive dates

For a timestamp column, use a start-inclusive, next-day-exclusive interval: timestamp ≥ start date at midnight, and timestamp < the day after the selected end date at midnight. This includes the end day’s late-night records without guessing its final fraction of a second.

Scope and setup

Use Google Sheets in a desktop browser and a practice tab named Events. This guide assumes actual Sheets date/time values, all interpreted in the same agreed time zone. It does not parse arbitrary timestamp text or convert UTC offsets.

Google’s QUERY function accepts a query-language string and a header count. Its query language distinguishes date and datetime literals. Use datetime for the date-and-time column in this exercise. QUERY documentation, query-language literals

1. Create timestamps without ambiguous date strings

Enter headings Timestamp, EventID, Units in A1:C1. Enter the following formulas and values in rows 2–7:

DATE creates the day, and TIME supplies its time component. Google’s official DATE and TIME references describe those numeric inputs.

Select A2:A7 and use Format → Number to apply a date-and-time display that includes seconds. The formatting step helps you see the test boundaries; it is not a text-to-date conversion. Google’s formatting guide

2. Set two date-only controls

In F1 enter Start date, G1 End date (inclusive), F2 =DATE(2026,10,14) and G2 =DATE(2026,10,15). Format F2:G2 as dates. Do not put a time of day into these controls.

A useful input check is =ISNUMBER(F2) and the same check for G2 and A2. Each should return TRUE. This checks numeric storage, not whether the value is the date you intended. Compare the displayed year, month and day too.

In H1 enter Upper bound, and H2 enter =G2+1. Expected H2 is 2026-10-16 at midnight. This is a calendar-day reporting interval, not a claim about elapsed hours across daylight-saving transitions.

3. Inspect the query text first

In F4, build the query string:

="select A,B,C where A >= datetime '"&TEXT(F2,"yyyy-mm-dd")&" 00:00:00' and A < datetime '"&TEXT(G2+1,"yyyy-mm-dd")&" 00:00:00' order by A"

For the controls above, it should read:

select A,B,C where A >= datetime '2026-10-14 00:00:00' and A < datetime '2026-10-16 00:00:00' order by A

The query uses column letters, not heading labels. Its literal format is fixed even if the sheet displays dates as 14/10/2026. Google query-language reference

TEXT formats the two control dates for the query string using Google’s documented date patterns. Do not apply TEXT to the source timestamp column merely to make it look consistent; that would create text keys instead of the date/time input this query expects.

4. Run the query and reconcile records

Leave A10:C20 empty. In A10 enter:

=QUERY(A1:C7,F4,1)

The final 1 identifies the single header row. Expected records are EVT-02, EVT-03, EVT-04, EVT-05 in that order, totaling 70 units. EVT-01 is before the start boundary; EVT-06 is exactly at the excluded upper boundary.

Why not use <= end date? A date-only end control represents midnight at the start of that day. An upper comparison at October 15 00:00:00 would include EVT-04 but miss EVT-05. Why not stop at 23:59:59? That creates an unnecessary assumption about time precision. The next-day exclusive bound is simpler.

5. Test the boundary changes

• Set both controls to October 15. Expected: EVT-04 and EVT-05, totaling 45.
• Set both to October 14. Expected: EVT-02 and EVT-03, totaling 25.
• Restore October 14–15. Expected: four rows, 70.
• Try an end date earlier than the start. Treat it as invalid input; do not interpret an empty report as “no activity.”

For a genuinely date-only source, use date '2026-10-14' literals rather than inventing a time component. Do not silently switch types to conceal a source-data problem.

Troubleshooting

The query parses incorrectly: Inspect F4 for missing quotes, misplaced spaces and an invalid date. If your locale uses semicolon function separators, adapt the Sheets formula separators; the query string’s select A,B,C syntax stays unchanged.

Rows disappear: Check whether source cells contain timestamp text rather than actual date/time values. QUERY’s mixed-type rules can treat minority types as null. See the existing QUERY reference before rebuilding a report around partially converted data.

Dates look shifted: Check File → Settings for locale and time zone, and confirm the source’s convention. Spreadsheet location settings do not replace a deliberate timezone-conversion policy for external UTC timestamps.

New events do not appear: Expand A1:C7 deliberately and rerun the record-count and total checks. This fixed practice range does not promise to include later rows automatically.

Testing disclosure: Documentation reviewed October 16, 2026. The interval membership and totals were independently calculated. This tutorial has not been tested in Google Sheets, including QUERY’s actual date/time typing and locale-specific UI behavior.