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]

Instructor 1	Cqt1	Off	Off	Cqt2	Cqt7	Cqt1	Cqt3	Cqt5	Cqt8
Instructor 2	Cqt2	Cqt2	Cqt1	Cqt3	Cqt5	Cqt6	Cqt4	Cqt2	Off
Instructor 3	Cqt3	Cqt3	Cqt2	Off	Cqt1	Cqt2	Cqt2	Cqt1	Off

<tbody>
</tbody>

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]

	A	B	C	D	E	F	G	H	I	J
1		2/1/17	2/2/17	2/3/17	2/4/17	2/5/17	2/6/17	2/7/17	2/8/17	2/9/17
2	Instructor 1	Cqt1	Off	Off	Cqt2	Cqt7	Cqt2	Cqt3	Cqt5	Cqt8
3	Instructor 2	Cqt2	Cqt2	Cqt2	Cqt2	Cqt2	Cqt2	Cqt2	Cqt2	Cqt2
4	Instructor 3	Cqt3	Cqt3	Cqt2	Off	Cqt1	Cqt2	Cqt2	Cqt21	Off

<tbody>
</tbody>

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!

	A	B	C	D	E	F	G	H	I	J
1		2/1/17	2/2/17	2/3/17	2/4/17	2/5/17	2/6/17	2/7/17	2/8/17	2/9/17
2	Instructor 1	Cqt1	Off	Off	Cqt2	Cqt7	Cqt2	Cqt3	Cqt5	Cqt8
3	Instructor 2	Cqt2	Cqt2	Cqt2	Cqt2	Cqt2	Cqt2	Cqt2	Cqt2	Cqt2
4	Instructor 3	Cqt3	Cqt3	Cqt2	Off	Cqt1	Cqt2	Cqt2	Cqt21	Off

<tbody>
</tbody>

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.