Wednesday, March 25, 2015

yogi_From Table Of Timestamps And Response Types Compute Number Of Responses Per Hour By Day

                                           Google Spreadsheet   Post  #1931
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    Mar 26, 2015
question by JacksonIsaiah:!category-topic/docs/spreadsheets/xvbxh3bnSJw
Issue with Format of Form Response Data and Query

Here is a sample sheet with Form Responses I am working on:

Thanks to all the help and information on these forums, I've learned a lot about sheets, and found this formula from Hyde (a big forum contributor) which should show the number of responses per hour:

=query( arrayformula( if({1,0}, A2:A, A2:A) ), "select toDate(Col1), count(Col2) where hour(Col1) >= 0 group by toDate(Col1) label toDate(Col1) 'Date', count(Col2) 'Number of responses' ", 0)

I'm getting a #VALUE error which states the query can't perform the hour function on a column that isn't TimeOfDay or DateTime. I've tried several number formats but can't seem to get it working.

Ideally, the query would show the number of responses per hour by day.

Any help would be greatly appreciated.