0

I've got a bunch of times in a google drive spreadsheet. e.g.:

6:25:20
8:24:20
13:49:05
15:11:40
15:16:56
19:12:10
20:31:40
20:48:44
21:00:23
21:34:05
23:33:25

(this is just a small example, but in reality there are 100's of times)

I want to group the times into blocks so I can analyse what times are most frequent. Eg. 0-3am, 3-6am, 6-9am etc...

e.g.:

0-3am : 0
3-6am : 0
6-9am : 2 
etc.. 

I'm believe the best way to do this is with a Pivot table, which I'm really used to using, but I've tried and cant get this to work, any ideas ?

I've created an example sheet below:
https://docs.google.com/spreadsheets/d/1IzNljlrr5RPagJ3HG0QksG4tlj7-QFQhFzLoIkLg1Ps/edit#gid=0

2 Answers 2

1

This is probably easiest to do with a query() formula that counts occurrences by timeslot, like this:

=arrayformula( 
  query( 
    { 
      text(int(A3:A * 24 / 3) * 3, "00") & ":00—" 
      & 
      text(int(A3:A * 24 / 3) * 3 + 2, "00:59"), 
      A3:A 
    }, 
    "select Col1, count(Col2) 
     where Col2 is not null 
     group by Col1 
     label Col1 'timeslot' ", 
    0 
  ) 
)

See the query() help page.

0

Another way to do something similar would be to add a column into a the data set with the following formula =FLOOR(A2,"3:00") which will round the time value to the nearest 3 hour interval. You can then run a pivot table over this rounded data.

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.