I use Google Sheets to manage the schedule of 17 rotating shift workers. The draft schedule runs out 9 months. It's easy for myself to loose track of folks working too many days in a row covering shifts with overtime due to workload demands and others taking leave. I'm looking to the count max number of days a person would work in a row as I wish to limit and avoid staff burnout. My initial thinking would be to count the number of consecutive days which are NOT a dash ("-") as a dash indicates a scheduled day off. It would also be wonderful to indicated corresponding dates of non-consecutive days (i.e. 15 dates between X and X).
This comes close to what I envision, but haven't played with it. Return longest streak of consecutive dates in a Google Sheets column
You may try:
=max(scan(,filter(D$2:O,D$1:O$1=U5),lambda(a,c,if(or(c="-",c="",c="AL"),,a+1))))
for consecutive days:
=let(Σ,scan(,filter(D$2:O,D$1:O$1=U5),lambda(a,c,if(or(c="-",c="",c="AL"),,a+1))),
Λ,+filter(B$2:B,Σ=max(Σ)),
to_text(Λ-max(Σ)+1)&" - "&to_text(Λ))