Maybe this will help you.
I used a helper column on the Master worksheet to count consecutive days following the rules. I an apparent run of consecutive days is interrupted by missing a day, the count starts again. With this amount of data I can't check everything but what I did check, was correct.
Formula for helper column (G) on Master worksheet. Enter in G2 and fill down. Column G must have a Title or it won't work. This is the most complicated part of the whole problem.
On Sheet2!B2 enter this formula and fill down.
On Sheet2!C2 enter this formula and fill down.
On Sheet2!D2 enter this formula and fill down.
I also created a small summary table totalling the absences for each rule. It should be self explanatory.
Bookmarks