0

I have a Google Sheet with a list of dates & times like this:

10/24/2019 4:25:00
10/29/2019 4:33:22
10/30/2019 4:33:11
10/31/2019 4:24:11
11/1/2019 4:36:11
11/2/2019 5:21:37
11/4/2019 5:30:20
11/6/2019 5:21:28
11/7/2019 5:27:07
11/8/2019 5:22:34
11/10/2019 8:51:13
11/11/2019 5:26:27
11/11/2019 11:41:27
11/12/2019 5:27:49
11/13/2019 5:30:34

I am looking for a way to figure out the longest "streak" of consecutive dates where there was at least one entry. The data can be unsorted or sorted.

  • What code do you have so far? – CodeCamper Nov 21 at 18:16
  • Please add a brief description of your search/research efforts as is suggested in How to Ask – Rubén Nov 21 at 20:07
1

Solution without google app scripts

1) sort your unique dates

=arrayformula(query(unique(datevalue(A:A)),"select * where Col1 is not null"))

2) mark the days that are streak days with a 1

=arrayformula(if(F:F="","",{0;if(F2:F-F1:F=1,1,0)}))

3) count the streaks

=max(arrayformula(if(G:G="","",frequency(if(G:G,row(G:G)),if(not(G:G),row(G:G))))))

Note: in my example column A is the original dates, column F is my sorted dates, column G is my streak marker. You can also combine step 2 and 3 into one bigger formula =arrayformula(frequency(if(if(F:F="","",{0;if(F2:F-F1:F=1,1,0)}),sequence(rows(if(F:F="","",{0;if(F2:F-F1:F=1,1,0)})))),if(not(if(F:F="","",{0;if(F2:F-F1:F=1,1,0)})),sequence(rows(if(F:F="","",{0;if(F2:F-F1:F=1,1,0)}))))))

  • I had to add a +1 to the formula in step 3 to get the total number of days that were in the streak, but other than that it worked well, thanks! – Tony Ranieri Nov 25 at 20:01
  • @TonyRanieri yes it depends on if you consider the first day a streak or not, if I defined it that way then everyday by definition would be a "1 streak". – CodeCamper Nov 26 at 1:13
1

You could create a google app script function like this:

function BIGGEST_CONSECUTIVE_DATE_STREAK() {
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = activeSheet.getActiveRange();
  var currentDate;
  var newDate;
  var biggestStreak = 0;
  var currentStreak = 0; 

  // Loop through all values in the selected range
  for (i = 0; i < range.getValues().length - 2; i++) {
    currentDate = new Date(range.getValues()[i])
    newDate = new Date(range.getValues()[i+1])

    if (currentDate.getMonth == newDate.getMonth) {  
      if (newDate.getDate() - currentDate.getDate() == 1) {
        // If dates are consecutive in terms of days, 
        // the consecutive date streak increases.
        currentStreak++;
      } else {
        // Otherwise the streak ends.
        currentStreak = 0;
      }
    }
    // Update the biggest number of consecutive days.
    if (currentStreak > biggestStreak) biggestStreak = currentStreak;  
  } 
  // Alert the biggest consecutive date streak. 
  // Change this line depening on your use case.
  Browser.msgBox(biggestStreak, Browser.Buttons.OK_CANCEL);
}

I presumed you meant by 'consecutive dates' consecutive as in days.

Usage: select the dates in google sheets and execute this script.

Go here for the documentation on how to create a custom function and link it to your google sheet if you don't know how to execute the script.

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.