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.

  • 1
    Try reading this question & answers: superuser.com/q/518632/179112
    – Jacob Jan
    Dec 14, 2012 at 10:01
  • @JacobJanTuinstra - Does that work for Google Spreadsheets?
    – Dave Rook
    Dec 14, 2012 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.
    – grssnbchr
    Dec 14, 2012 at 10:21
  • @DaveRook; see example file I've included.
    – Jacob Jan
    Dec 14, 2012 at 10:24
  • In the future, please ask questions about Google Spreadsheets here, not on Super User. Thanks!
    – slhck
    Dec 18, 2012 at 17:40

2 Answers 2


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:
  • 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).
    – grssnbchr
    Dec 26, 2012 at 9:17
  • I will add it to the file OK, as a bonus !! Thanks and good luck.
    – Jacob Jan
    Dec 26, 2012 at 9:19
  • @wnstnsmth: see sheet DATE
    – Jacob Jan
    Dec 26, 2012 at 11:07
  • fair enough.. thought there was a smoother way :-)
    – grssnbchr
    Dec 26, 2012 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.


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
    Dec 25, 2012 at 17:01
  • @wnstnsmth and Jasper: I've added the result into the file I created under sheet QUERY.
    – Jacob Jan
    Dec 25, 2012 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. Dec 26, 2012 at 12:53

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.