Currently I am able to Query my data by the date in a timestamp but now I want to narrow it down to the hour and the day

=COUNT(QUERY('Form Responses 1'!$A$2:$E, "Select A where A>=date '"&TEXT(B$5,"yyyy-mm-dd")&"' and A <= date '"&TEXT(B$6,"yyyy-mm-dd")&"'")

I am attempting to build a grid of when my responses come in by date and time over the hour Date ranges have to be dynamic. I need to go look at a sample of data over a week or a sample of data over a year

How can I add a conditional time and day statement to this?

This is what I want to do Each cell will be it's own query formula



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.

Your Answer

