Google Sheets Query with Date and Time

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryvlookup

I need help with a query. I need to filter on some Google Form data and only show the results that have been submitted AFTER a specific timestamp.

Timestamp Cell = J2 – This cell is in the following format mm/dd/yyyy hh:mm:ss

Day Query – This one seems to work ok but its only at the day level.

=query(FormResponses!A2:G,"Select * Where A > date '"&text(J2,"YYYY-mm-dd") & "'" )

Time of Day Query – This one also seems to work ok but only at the time level.

=query(FormResponses!A2:G,"Select * Where A > timeofday'" & text(J2, "HH:mm:ss") & "'" )

When I try to combine the two, it returns no results and I can not figure out why. I do see some wierd issues with the ">" in the queries above so i am second guessing the accuracy of the above queries to begin with. Does anyone have a sample of checking both date and time in a google sheets query?

Best Answer

=ARRAYFORMULA(VLOOKUP(TO_DATE(QUERY(VALUE(QUERY(FormResponses!A2:A, 
 "select *", 0)), 
 "select Col1 where Col1 > "&VALUE(J2), 0)), 
 {VALUE(FormResponses!A2:A), FormResponses!B2:G}, 
 {1, 2, 3, 4, 5, 6, 7}, 0))

Related Solutions

Google-sheets – How to use a query on a timestamp to filter down to “day of the week” and “hour”

To get the count of all timestamps (in column A) that refer to Monday between 7:00 and 8:00, use the query

=query(A1:A, "select count(A) where dayofweek(A) = 2 and hour(A) = 7 label count(A) ''")

This where clause can be combined with inequalities such as where A <= date '2015-12-31' to specify the date range.

Since you have a bunch of queries, you probably don't want to hardcode the day and hour numbers in each. For the hours, assuming the column B is the leftmost column your table, the following will work:

... and hour(A) = " & hour($B2) & " label ...

For the day of week, it's easiest to add a hidden helper row with numbers 2,3,4,5,6,7,1 corresponding to Monday, Tuesday, and so on till Sunday. Then refer to that row with

... where dayofweek(A) = " & C$3 & " and ...

One can also use a lookup table for this, or even cook-up a formula to put together a string with full date and parse it to extract weekday, but this is more work.

Google-sheets – QUERY most recent date prior to today

I would use filter instead of query. The function

=counta(filter(S1:AF, R1:R<=today(), R1:R>today()-14))

counts the number of nonempty entries in the row with the date that falls after today()-14 but not later than today(). The logic is simple: filter the rows, returning the content of their columns S through AF, then apply counta.

To get the count of entries with "y", a second filter is used:

=counta(filter(filter(S1:AF, R1:R<=today(), R1:R>today()-14),  S1:AF1="y"))

The final result, the proportion, is obtained by dividing the latter by the former:

=counta(filter(filter(S1:AF, R1:R<=today(), R1:R>today()-14),  S1:AF1="y")) / counta(filter(S1:AF, R1:R<=today(), R1:R>today()-14))

Best Answer

Related Solutions

Google-sheets – How to use a query on a timestamp to filter down to “day of the week” and “hour”

Google-sheets – QUERY most recent date prior to today

Related Topic