Return longest streak of consecutive dates in a Google Sheets column

Question

I have a Google Sheets file with data that looks like this:

dates in a column

It's a column with dates. I'm trying to find a single cell formula that's able to calculate the longest streak of consecutive days within this column.

I've tried searching for it, but couldn't find it. I suppose this requires a complicated query/arrayformula combination...

I would be able to do it with the help of dummy columns, but this is for an interactive dashboard so I want to keep it as clean as possible. I hope that makes sense...

Here's a document that includes the example data, and the preferred outcome: https://docs.google.com/spreadsheets/d/14h241HNpgqq8T5cizR8UPF0NgUSm_kBb6RWpnnPSQLc/edit?usp=sharing

Any help would be really appreciated!

user_3894917 · Answer

I have been working on the basis that you could use the ideas from here but it wasn't as straightforward as I first thought because you don't have gaps in the list of dates, you just have a jump from one series of dates to the next. However I think that you can still use Frequency something like this:

=ArrayFormula(frequency(if(countif(A5:A23,A5:A23-1),row(A5:A23)),if(countif(A5:A23,A5:A23-1)=0,row(A5:A23))))

So I am labelling the start of each streak with a zero like this:

The frequencies come out like this:

As you can see, each frequency count is one less than the length of the streak associated with it. This is because I am using the 'zeroes' as the bin ranges in the frequency so I lose one data point in each group. I think it's sufficient just to add 1 to each group to get the actual streak length so my proposed formula for maximum streak length is:

=ArrayFormula(max(frequency(if(countif(A5:A,A5:A-1),row(A5:A)),if(countif(A5:A,A5:A-1)=0,row(A5:A)))+1))

Since the number of groups is one more than the number of zeroes, the most recent streak is given by:

=ArrayFormula(index(frequency(if(countif(A5:A,A5:A-1),row(A5:A)),if(countif(A5:A,A5:A-1)=0,row(A5:A)))+1,countifs(countif(A5:A,A5:A-1),0,A5:A,"<>")+1))

To be done

I think having got this far I should be able to get the start and end of the longest and most recent streaks.

user_5632629 · Answer

try:

=INDEX(COLUMNS(SPLIT(FLATTEN(SPLIT(TRIM(QUERY(
 IF(A5:A-A6:A=-1, 1, 0),,9^9)), " 0 ", )), " "))+1)

alternative:

=ARRAYFORMULA(MAX(LEN(SUBSTITUTE(FLATTEN(SPLIT(QUERY(IF(
 FILTER(A5:A-A6:A, A5:A<>"")=-1, "♦", "♦×"),,9^9), "×")), " ", ))))

=ARRAYFORMULA(REGEXEXTRACT(QUERY(LEN(SUBSTITUTE(FLATTEN(SPLIT(QUERY(IF(
 FILTER(A5:A-A6:A, A5:A<>"")=-1, "♦", "♦×"),,9^9), "×")), " ", )),,9^9), "\d+$")*1)

Return longest streak of consecutive dates in a Google Sheets column

2 Answers