Locate any consecutive cells in a row (where employee worked)

  • 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. ?(

    Code
    1. =COUNTBLANK(C6:XDJ6)+COUNTIF(C6:XDJ6,"=Off")+COUNTIF(C6:XDJ6,"=V")+COUNTIF(C6:XDJ6,"=X")=0

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


    Thank you,

    David