9

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.

|improve this question

migrated from superuser.com Dec 18 '12 at 17:39

This question came from our site for computer enthusiasts and power users.

  • 1
    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
  • In the future, please ask questions about Google Spreadsheets here, not on Super User. Thanks! – slhck Dec 18 '12 at 17:40
6
+50

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

|improve this answer
  • 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
-1

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 --

|improve this answer
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.