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 '19 at 18:16
• Please add a brief description of your search/research efforts as is suggested in How to Ask – Rubén Nov 21 '19 at 20:07

``````=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 '19 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 '19 at 1:13

You could create a google app script function like this:

``````function BIGGEST_CONSECUTIVE_DATE_STREAK() {
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.