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.
Best Answer
I think I've got the solution for you. It's not done all automatically, but it will result in your proposal:
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 toDD-MM-YYYY
.=UNIQUE(ARRAYFORMULA(DATEVALUE(I2:I)))
='Original Data'!J2+TIME(18;0;0)
You need to choose the intervals wisely.
=A3+TIME(6;0;0)
=FREQUENCY('Original Data'!I2:I;A3:A)
See example file I've prepared: Frequencies