I have setup google sheet query function to filter data with matching date but I am facing a different issue that I do not have any solution to and would appreciate help.

I have records for emails received at different time for different users so my sheet looks like this

Date and Time	Email Received
3/4/2025 09:43:00	[email protected]
3/4/2025 10:01:00	[email protected]
3/4/2025 10:08:00	[email protected]
3/4/2025 12:52:00	[email protected]
3/4/2025 14:16:00	[email protected]
3/4/2025 15:13:00	[email protected]
3/4/2025 16:18:00	[email protected]
3/4/2025 18:00:00	[email protected]
3/5/2025 09:15:00	[email protected]
3/5/2025 09:52:00	[email protected]
3/5/2025 10:12:00	[email protected]
3/5/2025 11:37:00	[email protected]
3/5/2025 11:55:00	[email protected]
3/5/2025 13:05:00	[email protected]
3/5/2025 14:20:00	[email protected]
3/5/2025 20:00:00	[email protected]

I have setup google sheet query function to filter data with matching date but I am facing a different issue that I do not have any solution to and would appreciate help.

I have records for emails received at different time for different users so my sheet looks like this

Date and Time	Email Received
3/4/2025 09:43:00	[email protected]
3/4/2025 10:01:00	[email protected]
3/4/2025 10:08:00	[email protected]
3/4/2025 12:52:00	[email protected]
3/4/2025 14:16:00	[email protected]
3/4/2025 15:13:00	[email protected]
3/4/2025 16:18:00	[email protected]
3/4/2025 18:00:00	[email protected]
3/5/2025 09:15:00	[email protected]
3/5/2025 09:52:00	[email protected]
3/5/2025 10:12:00	[email protected]
3/5/2025 11:37:00	[email protected]
3/5/2025 11:55:00	[email protected]
3/5/2025 13:05:00	[email protected]
3/5/2025 14:20:00	[email protected]
3/5/2025 20:00:00	[email protected]

I want to write a query function to show all emails received on the selected date on a cell.

If my date does not have time, I am able to get it to work but as my data has datetime format and my filter criteria does not have time, how do I get around and convert my data column from datetime to date so I can filter is based on the date.

I am also attaching the link for this reference document. https://docs.google/spreadsheets/d/1TPilDEwvUXCrJ51plO46geBJj7cPJ4rWkV_rFNbkFYQ/edit?usp=sharing

Would appreciate help in getting to know how do I change the function to make it work.

Thank you

• google-sheets
• google-sheets-formula

Share Improve this question Follow edited Mar 6 at 8:21 TheMaster 51.3k77 gold badges7373 silver badges100100 bronze badges asked Mar 5 at 21:32 TaizooooonTaizooooon 10122 gold badges55 silver badges1111 bronze badges 1

• 1 It seems to me that your question has already been addressed. As a general rule in StackOverflow, if your concern is solved please accept an answer which works best for you by clicking the check mark beside the answer. This is to let other members from the community with the same concern as yours know that the issue is resolved. How to accept answer – leylou Commented Mar 11 at 18:31

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 1

You may also try this:

=QUERY(Data!A1:B, "SELECT * WHERE Col1 CONTAINS DATE '" & TEXT(B1, "YYYY-MM-DD") & "'" & IF(B2 = "", "", " AND Col2 = '" & B2 & "'") & " ORDER BY Col1", 1)

This checks B1 with B2 as an optional parameter.

OUTPUT

A	B
Select Date	3/4/2025
Select Email
Date and Time	Email Received
3/4/2025 9:43:00	[email protected]
3/4/2025 10:01:00	[email protected]
3/4/2025 10:08:00	[email protected]
3/4/2025 12:52:00	[email protected]
3/4/2025 14:16:00	[email protected]
3/4/2025 15:13:00	[email protected]
3/4/2025 16:18:00	[email protected]
3/4/2025 18:00:00	[email protected]

To use two search parameters as shown in the sample spreadsheet, where both parameters are optional, use filter(), like this:

=filter(
  Data!A2:B, 
  (B1 = "") + (B1 = datevalue(Data!A2:A)),
  (B2 = "") + (B2 = Data!B2:B)
)

See filter() and datevalue().

You may try:

=query(Data!A2:B,"where todate(A)=date'"&text(B1,"yyyy-mm-dd")&"'",0)

OR

=filter(Data!A:B,int(Data!A:A)=B1)