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>

 

[etaf]

i would use an offset() and a countif()

so assuming your days start at column B and row2
Also assuming 1 week of work is 5 days ??

then I would select F2:NB?? how ever many employees

now for RULE 1
use a formula
=COUNTIF(OFFSET(F2,0,-5,1,5),"X")=5

this is going back 5 columns and 1 row and looking at the range and counting the "X" to see if they equal 5 , if they do then the 6th cell will highlight

if that works then you can setup the following 4 rules and test for larger ranges
25 days would be

=COUNTIF(OFFSET(F2,0,-25,1,25),"X")=25

you can put the last rule 1st and stop if true
and then the other rules in reverse order and stop if true

heres a sample with just 2 rules
5 days and 24 days

https://www.dropbox.com/s/ipbscmu5h5f5bf1/cond_frmt_rest_days_etaf.xlsx?dl=0

 

[etaf]

i had to update the formula and now you can start from B2

This is for 24 days and highlights the the next 4 DAYS , or more if go over the 24 dyas

i'm sure others will have a more elegant solution

=OR(IFERROR(COUNTIF(OFFSET(B2,0,-24,1,24),"X")=24,FALSE),IFERROR(COUNTIF(OFFSET(B2,0,-25,1,24),"X")=24,FALSE),IFERROR(COUNTIF(OFFSET(B2,0,-26,1,24),"X")=24,FALSE),IFERROR(COUNTIF(OFFSET(B2,0,-27,1,24),"X")=24,FALSE))

i have updated the link to provide all 5 rules

Green
Yellow
Amber
Brown
RED

 

[diem69]

Thank you Wayne! I'm anxious to try this out. Just back to work after the Christmas holiday's so everything is backed up a little. I will be sure to post a reply once I've had an opportunity to apply the formulas.

Michelle

i had to update the formula and now you can start from B2

This is for 24 days and highlights the the next 4 DAYS , or more if go over the 24 dyas

i'm sure others will have a more elegant solution

=OR(IFERROR(COUNTIF(OFFSET(B2,0,-24,1,24),"X")=24,FALSE),IFERROR(COUNTIF(OFFSET(B2,0,-25,1,24),"X")=24,FALSE),IFERROR(COUNTIF(OFFSET(B2,0,-26,1,24),"X")=24,FALSE),IFERROR(COUNTIF(OFFSET(B2,0,-27,1,24),"X")=24,FALSE))

i have updated the link to provide all 5 rules

Green
Yellow
Amber
Brown
RED

 

[diem69]

Hello Wayne,

I copied your formula's to my spreadsheet so not to make any errors in entering. Now that I've had the opportunity to test with actual situations and our employee's work schedule, it seems for the most part it is working as needed, however it appears that it is not ensuring the days of rest are "consecutive" as required when reaching lengthy periods (ex Carson) I don't know if this is something that can be resolved or if my request is too complicated. Please advise. Thank you!!

https://www.dropbox.com/s/9n7kl2u9vbn3ciy/payroll hrs of service.xlsx?dl=0.

Thank you Wayne! I'm anxious to try this out. Just back to work after the Christmas holiday's so everything is backed up a little. I will be sure to post a reply once I've had an opportunity to apply the formulas.

Michelle

 

[etaf]

would you mind explaining and show where its wrong in the sample

what do you expect to happen with Carson

 

[diem69]

Hi Wayne,

Once Carson reaches the 3rd week, he has one day off (V8) and then works 3 days, then has one day off (Z8) and then the cells stop changing color. But since he hasn't taken 3 consecutive days off, I think the cells should still be adding color as per hours of service rule "3 consecutive days of rest in each period of 3 consecutive weeks". It stills works really well because the colored cells act as a warning, but I worry that Carson will exceed the hours allowable because the cell returned to "no color" which would mean to our dispatcher that he has had his required "reset" of days off.

I appreciate your help Wayne. I know it's rather confusing, but I hope my explanation helps.

Michelle

 

[etaf]

the colours are flagging that the days of rest have NOT been taken
so
is there some sort of override rule we can put in, that looks at the last total number of CELLS
say 24 and then count the number of consecutive blanks and mark RED

i'm just not sure how that algorithm would work out at the moment

so in carlson case
18 consecutive X
then a blank - so because a day has been taken , there has to be a minimum of blanks now
that number of blanks will be based on the previous number of consecutive X , in this example 18
so how many blanks should now follow

ie V5 is blank and
W5 is NOT - so that should be RED , NOT allowed to work.

 

[diem69]

Hi Wayne,

So just to recap,
- after 5 X's and 1 blank, everything resets (no color in cells) or after 5 X's and no blank, cells turn yellow.
- after maximum of 10 X's and 2 consecutive blanks - everything resets (no color in cells), or with less than 2 consecutive blanks, cells turn green.
- after maximum of 15 X's and 3 consecutive blanks - everything resets (no color in cells), or with less than 3 consecutive blanks, cells turn peach.
- after maximum of 21 X's and 4 consecutive blanks - everything resets (no color in cells), or with less than 4 consecutive blanks, cells turn steel blue.
- after maximum of 24 X's and 4 consecutive blanks - everything resets (no color in cells), or without 4 consecutive blanks, cell turns red.

 

[etaf]

i have looked into stopping the colours appearing in the "clear" cells after the set number of X's so far no solution yet
Disptite the colours appearing
so 5x's then yellow if clear , needs 5 X;s again
10x same 2 green
15x same 3 peach
21x same 4 steels
then red
can you give the example again where its not correctly at all