Making ranges based on variable data

[Jiyala]

Hi All,

I am stuck with a problem for 2 days and I can not find a solution to it. I am trying to make an employee roster with variable constraints. Each employee will get a rest day after 5 days provided the 6th day is not Friday or Sunday. If the 6th day is Friday or Sunday, the employee gets a day off after 4 days (a day before Friday or Sunday).

=IF(COUNTIF($C$3:C3,"5:15")<4,"5:15",IF(COUNTIF($C$3:C3,"5:15")=4,IF(OR(E2="Friday",E2="Sunday"),"R","5:15"),IF(COUNTIF($C$3:C3,"5:15")=5,IF(OR(E2="Friday",E2="Sunday"),"R","5:15"))))

I have managed to get this far but I need the range to change after "Rest" appears in the schedule. In my current formula, the range keeps expanding from C3 onwards and the numerical counter does not work. Is there a way that I can keep the range variable i.e. If previous cell (G3) = Rest, the range of the formula should change to the Rest cell onwards ($G$3:G3).

Would be really great if someone has some other way to do this as well!

[tim201110]

b4 =IF(OR(AND(COUNTIF(INDEX(B:B,AGGREGATE(14,6,ROW($A$1:$A3)/(B$1:B3="r"),1)):B3,"o")=5,OR(WEEKDAY($A4,2)<>5,WEEKDAY($A4,2)<>7)),AND(COUNTIF(INDEX(B:B,AGGREGATE(14,6,ROW($A$1:$A3)/(B$1:B3="r"),1)):B3,"o")=4,OR(WEEKDAY($A4,2)=5,WEEKDAY($A3,2)=7))),"r","o")
in b3 - r
in A:A dates

[Jiyala]

Thanks Tim.

Couple of questions:

1. The formula is still showing the rest day on a Sunday. I need no rests on Sunday and Friday.

2. I am attaching the sheet I am working on. Can you review and help out?

[Entregan]

You might try putting the Non-Rest days condition at the front of the code.

Please Login or Register  to view this content.

[tim201110]

small error
=IF(OR(AND(COUNTIF(INDEX(B:B,AGGREGATE(14,6,ROW($A$1:$A3)/(B$1:B3="r"),1)):B3,"o")=5,WEEKDAY($A4,2)<>5,WEEKDAY($A4,2)<>7),AND(COUNTIF(INDEX(B:B,AGGREGATE(14,6,ROW($A$1:$A3)/(B$1:B3="r"),1)):B3,"o")=4,OR(WEEKDAY($A4,2)=4,WEEKDAY($A4,2)=6))),"r","o")

[tim201110]

a shorter one
=IF(CHOOSE(COUNTIF(INDEX(B:B,AGGREGATE(14,6,ROW($A$1:$A3)/(B$1:B3="r"),1)):B3,"o")+1,,,,,5,6)+CHOOSE(WEEKDAY($A4,2),1,1,1,2,,2,)>=7,"r","o")