Search code examples
google-sheetsjob-scheduling

Count Number of Non Consecutive Occurrence of values in Google sheets

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

sample rotating schedule

This comes close to what I envision, but haven't played with it. Return longest streak of consecutive dates in a Google Sheets column

Solution

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

    enter image description here

    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(Λ))