How to Filter Timestamp in Query in Google Sheets

You can filter timestamp in Query in Google Sheets and also can use comparison operators with the filtering. Suppose my column A contains the timestamp and I want to filter this column between two dates and time, you can do it using the function Query.

In Google Sheets, you can use comparison operators in a column containing the timestamps without modifying the column content. Without modifying the column here means without splitting the timestamp to date and time.

You may only need to split the timestamp date and time if you want to summarise your data month, year or quarter wise.

Similar: Create Month Wise Summary in Google Sheets Using Query Formula

How to Filter Timestamp in Query in Google Sheets

Sample Data:

example to filter by timestamp in Query in Google Sheets

In this Google Sheets Query example, I want to filter the data in Column A and B based on the criteria in the cells D2 and E2.

In D2 and E2 you can see a start time and end time. Actually, it’s date and time, means a timestamp.

The Formula to Filter by Date and Time (timestamp) in Google Sheets

Here it is.

=query(A1:B,"Select A,B where A>= datetime '"&TEXT(D2,"yyyy-mm-dd HH:mm:ss")&"' and A<= datetime '"&TEXT(E2,"yyyy-mm-dd HH:mm:ss")&"'")

You can follow this formula when you want to filter by date and time in Query in Google Sheets.

Formula Explanation

In this formula, that filter Timestamp in Query in Google Sheets, the Cells D2 (start date and time), and E2 (end date and time) contain the criteria.

To use a timestamp as Criteria in Query, you should convert this timestamp (criteria) to text format. The formatting should be as follows.

The year should be in the format,YYYY-MM-DD and the time in the formatHH:mm:ss.

The below formula adhere to this rule and that is what I’ve followed in the above Query filter formula.

=TEXT(D2,"yyyy-mm-dd HH:mm:ss")

Since it’s a text, follow the normal method that you use when you want to refer to a cell containing text in Query as criteria. That is as follows.

B='"&D2&"'"

Here the cell address D2 is replaced by the above text formula. To identify this text formula as a date and time value, in the Query formula use the keyword “DateTime” before it.

datetime '"&TEXT(D2,"yyyy-mm-dd HH:mm:ss")&"'

Hope this makes sense. Those who are looking for more details about how to use the date as the criterion in Query, please follow my below tutorial.

How to Use Date Criteria in Query Function in Google Sheets [Date in Where Clause]

That’s all about how to filter Timestamp in Query in Google Sheets. Enjoy!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.