# Analyse frequencies of date ranges

I have a Google Spreadsheets where I would like to compute occurrences of date ranges. I have a column `date_utc+1` which contains almost random date data.

What I would like to do is put the date values into bins of six hours each, i.e., `12/5/2012 23:57:04` until `12/6/2012 0:03:17` would be in the first bin, `12/6/2012 11:20:53` until `12/6/2012 17:17:07` in the second bin, and so forth. Then, I would like to count the occurrence of those bins, such as:

``````bin_from            bin_to                freq
-----------------------------------------------
12/5/2012 23:57:04  12/6/2012 0:03:17     2
12/6/2012 11:20:53  12/6/2012 17:17:07    19
...                 ...                   ...
``````

Partial hints are very welcome as well since I am pretty new to spreadsheeting.

• Try reading this question & answers: superuser.com/q/518632/179112 – Jacob Jan Tuinstra Dec 14 '12 at 10:01
• @JacobJanTuinstra - Does that work for Google Spreadsheets? – Dave Rook Dec 14 '12 at 10:12
• @JacobJanTuinstra this actually exactly solves my question superuser.com/questions/519286/…, you could answer it with a link to your answer in the other question or mark it as a duplicate. – wnstnsmth Dec 14 '12 at 10:21
• @DaveRook; see example file I've included. – Jacob Jan Tuinstra Dec 14 '12 at 10:24

I think I've got the solution for you. It's not done all automatically, but it will result in your proposal:

• In your file the date is written as `MM-DD-YYYY`. I made the assumption that the date range covers december and NOT random dates throughout the year. Therefore I had to re-arrange the date format to `DD-MM-YYYY`.
• As a guidance I used the following formula to get the unique date range: `=UNIQUE(ARRAYFORMULA(DATEVALUE(I2:I)))`
• In the FREQUENCY sheet I used this formula to calculate the first interval:
`='Original Data'!J2+TIME(18;0;0)`
You need to choose the intervals wisely.
• Copy down this formula untill the desired interval range is completed:
`=A3+TIME(6;0;0)`
• Use the FREQUENCY formula to calculate your result:
`=FREQUENCY('Original Data'!I2:I;A3:A)`

See example file I've prepared: Frequencies

• Makes perfect sense, thank you! Last question: How did you easily convert the dates from MM-DD-YYYY to DD-MM-YYYY (I don't see any formula). – wnstnsmth Dec 26 '12 at 9:17
• I will add it to the file OK, as a bonus !! Thanks and good luck. – Jacob Jan Tuinstra Dec 26 '12 at 9:19
• @wnstnsmth: see sheet DATE – Jacob Jan Tuinstra Dec 26 '12 at 11:07
• fair enough.. thought there was a smoother way :-) – wnstnsmth Dec 26 '12 at 12:02

Using the query language and a group by clause should do the trick.

Something like: `=QUERY(A1:A509, "select count(B) group by year(A)")` I will try to create a working query later today.

EDIT:

I have inserted a new column left of column A. This column has the formula : `=year(A2) & "-" & minute(A2)`. This is just a quick test to create some ranges, this column should reflect the logic of the bins.

The formula `=Query(A:E;"select B, count(A) group by B")` will calculate the frequencies.

-- disclaimer, this is just an example, some tweaking is required to make it calculate the exact functionality mentioned in the question --

• In order for `select count(B)` to work, you need to prepare the range accordingly: `A:B`. Secondly, in Switzerland most probably the semicolon is used in stead off the comma. – Jacob Jan Tuinstra Dec 25 '12 at 17:01
• @wnstnsmth and Jasper: I've added the result into the file I created under sheet QUERY. – Jacob Jan Tuinstra Dec 25 '12 at 21:37
• @Jacob, I've also completed my solution in the sheet (Columns K,L,M). Any comments on this solution? As my answer was down-voted, I would like to know if I am missing something here. – Jasper Duizendstra Dec 26 '12 at 12:53