Conditional Formation with Consecutive cells for Hours of Work

[diem69]

<br>
Hello!<br>
<br>
I would like some assistance with a Conditional Formula that I've been struggling with for some time now. There are five rules that need to be followed that affect the color of the cell and only once the last rule is broken, should the cell be RED. I have employee names listed on each row, 365 days on columns, and an "x" is placed in each cell for employee/date that the employee works - otherwise left blank. The blanks would represent the "days of rest" as per rules below. <br>
<br>
The rules that must be followed are:<br>
- 1 day of rest for each week of work;<br>
- 2 consecutive days of rest in each period of 2 consecutive weeks,<br>
- 3 consecutive days of rest in each period of 3 consecutive weeks,<br>
- 4 consecutive days of rest in each period of 4 consecutive weeks, and<br>
- after 24 consecutive days of work, employees must be provided with at least 4 consecutive days of rest.<br>
<br>
<br>

 

[diem69]

The colors are correct (although I made a typo... shld be 20x then steel). The only example I had is Carson and again, it is only because the cells cleared, yet he didn't take the required 4 consecutive days off (4 blanks in a row).

https://www.dropbox.com/s/qg5ffpud8gsl140/Payroll Hrs of Service 2.xlsx?dl=0

If "clearing" the cells can't be changed based on the blanks, I still thank you as this is better than nothing. It still triggers the dispatcher to watch those that have reached the steel color and they can figure out to give the employee the required consecutive days off based on that.

Thank you again for your assistance!

Michelle