Thanks for taking your time to read. I would love to have this working, it would help so much! (1 Excel file attached)
When scheduling, I would like a person's workdays to highlight (turn red with a yellow stripe) if they are accidentally scheduled 7 or more days in a row in any of the 4-week (28 day period).
So it should highlight and let the user know if someone doesn't have at least 1 day off duty in any 7 consecutive days.
Off days can be any of the following four: Blank-not scheduled, Off - Off duty, V - Vacation, X - Not allowed to work
My conditional formatting code below says "If the number of days off (Blanks, Offs, V's, or Xs) in that 7 day range = 0, then highlight because that person would be overworked." The code below actually seems to work in highlighting the first day of any 7 day ranges, but not the entire set of 7 in a row days.
The 2nd bonus question for this formula (well bonus for me) is that we have 13 sheets of these 28 day schedules that make up the year. So I really also have to check that the days at the beginning and at the end of this schedule also don't go into 7 days when connecting to the prior and next schedule on other sheets. Best way to do that?? Would be easy if they were one big spreadsheet but...