3

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

Photo

1 Answer 1

1

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

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.