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.

2

2 Answers 2

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

2
  • 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! Nov 25, 2019 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, 2019 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 and acknowledge you have read our privacy policy.

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