Following up on this question which was answered by Player0, how would we write a formula to find the last date in the longest consecutive streak, which would be July 12?
And similarly, is there a way to find the last date of the latest streak, which would be July 23?
I’ve tried various ways of using INDEX and MATCH, along with other functions, and nothing’s been working so far.
Here's one approach:
=let(Σ,A5:A,
Λ,scan(,Σ,lambda(a,c,ifs(c="",,row(c)=row(Σ),1,c-1=offset(c,-1,),a+1,1,1))),
filter(Σ,Λ=max(Λ)))