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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!