Scheduling

[Bjudd07]

I am creating a schedule for instructors, but there are specific parameters such as a requirement that there must be a 24 hour break in a 7 day period. I create the schedule with names in the far left column and the days of the month in the top row from left to right. I would like a conditional formatting to let me know if anyone has a 24/7 violation so that I can revisit day off awards before I begin assigning positions for each day. Any help is appreciated!

 

[Scott T]

How are you recording that a instructor is working or not working? It is helpful to show some sample data.

 

[Bjudd07]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Instructor 1[/TD]
[TD]Cqt1[/TD]
[TD]Off[/TD]
[TD]Off[/TD]
[TD]Cqt2[/TD]
[TD]Cqt7[/TD]
[TD]Cqt1[/TD]
[TD]Cqt3[/TD]
[TD]Cqt5[/TD]
[TD]Cqt8[/TD]
[/TR]
[TR]
[TD]Instructor 2[/TD]
[TD]Cqt2[/TD]
[TD]Cqt2[/TD]
[TD]Cqt1[/TD]
[TD]Cqt3[/TD]
[TD]Cqt5[/TD]
[TD]Cqt6[/TD]
[TD]Cqt4[/TD]
[TD]Cqt2[/TD]
[TD]Off[/TD]
[/TR]
[TR]
[TD]Instructor 3[/TD]
[TD]Cqt3[/TD]
[TD]Cqt3[/TD]
[TD]Cqt2[/TD]
[TD]Off [/TD]
[TD]Cqt1[/TD]
[TD]Cqt2[/TD]
[TD]Cqt2[/TD]
[TD]Cqt1[/TD]
[TD]Off[/TD]
[/TR]
</tbody>[/TABLE]

In this example, the position the instructors teach are "cqt#" and their days off are labeled "Off". Instructors can only work 6 days in a row with out a day off. Instructor 2 has been scheduled more than 6 days in a row, so it would be nice if conditional formatting or a formula could detect an error if there are more than 6 cells consecutively that do not read "Off".

This is a small picture of what the calendar looks like since we schedule one month out, so all 30 days of the calendar are scheduled with so much text that it may be possible due to human error to create a 24/7 violation. Also, there are over 30 instructors, so it is a very tediuous talks, and not all I structure are qualified to teach each course, so it must be done manually.

How are you recording that a instructor is working or not working? It is helpful to show some sample data.

 

[Scott T]

[TABLE="class: grid, width: 300"]
<tbody>[TR="bgcolor: #DCE6F1"]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"]J
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]1
[/TD]
[TD][/TD]
[TD]2/1/17
[/TD]
[TD]2/2/17
[/TD]
[TD]2/3/17
[/TD]
[TD]2/4/17
[/TD]
[TD]2/5/17
[/TD]
[TD]2/6/17
[/TD]
[TD]2/7/17
[/TD]
[TD]2/8/17
[/TD]
[TD]2/9/17
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]2
[/TD]
[TD]Instructor 1
[/TD]
[TD]Cqt1
[/TD]
[TD]Off
[/TD]
[TD]Off
[/TD]
[TD]Cqt2
[/TD]
[TD]Cqt7
[/TD]
[TD]Cqt2
[/TD]
[TD]Cqt3
[/TD]
[TD]Cqt5
[/TD]
[TD]Cqt8
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]3
[/TD]
[TD]Instructor 2
[/TD]
[TD]Cqt2
[/TD]
[TD]Cqt2
[/TD]
[TD]Cqt2
[/TD]
[TD]Cqt2
[/TD]
[TD]Cqt2
[/TD]
[TD]Cqt2
[/TD]
[TD]Cqt2
[/TD]
[TD]Cqt2
[/TD]
[TD]Cqt2
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]4
[/TD]
[TD]Instructor 3
[/TD]
[TD]Cqt3
[/TD]
[TD]Cqt3
[/TD]
[TD]Cqt2
[/TD]
[TD]Off[/TD]
[TD]Cqt1
[/TD]
[TD]Cqt2
[/TD]
[TD]Cqt2
[/TD]
[TD]Cqt21
[/TD]
[TD]Off[/TD]
[/TR]
</tbody>[/TABLE]


I think this is what you want.
Select the H2:AC99, where AC99 is the end of the month and all the instructors in Conditional Formatting use this formula and select your formatting

=ISERROR(MATCH("Off",B2:H2,0))

If there is no Off in the prior 7 cells this will format the 7th cell. H3 is formatted because B3:H3 does not contain Off. I3 is formatted because C3:I3 does not contain Off and so on.

 

[Bjudd07]

Thank you! That works!

[TABLE="class: grid, width: 300"]
<tbody>[TR="bgcolor: #DCE6F1"]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]1[/TD]
[TD][/TD]
[TD]2/1/17[/TD]
[TD]2/2/17[/TD]
[TD]2/3/17[/TD]
[TD]2/4/17[/TD]
[TD]2/5/17[/TD]
[TD]2/6/17[/TD]
[TD]2/7/17[/TD]
[TD]2/8/17[/TD]
[TD]2/9/17[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]2[/TD]
[TD]Instructor 1[/TD]
[TD]Cqt1[/TD]
[TD]Off[/TD]
[TD]Off[/TD]
[TD]Cqt2[/TD]
[TD]Cqt7[/TD]
[TD]Cqt2[/TD]
[TD]Cqt3[/TD]
[TD]Cqt5[/TD]
[TD]Cqt8[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]3[/TD]
[TD]Instructor 2[/TD]
[TD]Cqt2[/TD]
[TD]Cqt2[/TD]
[TD]Cqt2[/TD]
[TD]Cqt2[/TD]
[TD]Cqt2[/TD]
[TD]Cqt2[/TD]
[TD]Cqt2[/TD]
[TD]Cqt2[/TD]
[TD]Cqt2[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]4[/TD]
[TD]Instructor 3[/TD]
[TD]Cqt3[/TD]
[TD]Cqt3[/TD]
[TD]Cqt2[/TD]
[TD]Off[/TD]
[TD]Cqt1[/TD]
[TD]Cqt2[/TD]
[TD]Cqt2[/TD]
[TD]Cqt21[/TD]
[TD]Off[/TD]
[/TR]
</tbody>[/TABLE]


I think this is what you want.
Select the H2:AC99, where AC99 is the end of the month and all the instructors in Conditional Formatting use this formula and select your formatting

=ISERROR(MATCH("Off",B2:H2,0))

If there is no Off in the prior 7 cells this will format the 7th cell. H3 is formatted because B3:H3 does not contain Off. I3 is formatted because C3:I3 does not contain Off and so on.