Manadatory break after 10 days of work formulae

[Scott]

Through the assistance of the exceptionally talented moderators/
contributors of this group, I was provided with the formulae shown
below. It works to assisst me in scheduling crews of workers based on
a 10-4 or (10 days on and 4 days off) or a 21-7 (21 days on and 7 days
off) schdule. A new twist now requires that I modify this formulae
slightly to include a mandatory 2 day break following 10 days of work.
As such, the 10-4 schedule works just fine, but I need to have two
"off" days appear after 10 working days for those people working the
21-7 shift.


Thank you in advance for any help that you may have to offer.
Regards
Scott


IF($G5>0,IF($F5="10-4",IF(MOD(AZ$3-$G5,14)+1<=10,"on","off"),IF(MOD(AZ
$3-$G5,28)+1<=21,"on","off")),"")

 

[joel]

Can you post what data you have in columns f, g, and AZ. It is hard t
figure what you have in each of these columns from the formula. I can'
figure why column G would be negative.

 

[Scott]

Can you post what data you have in columns f, g, and AZ.  It is hard to
figure what you have in each of these columns from the formula.  I can't
figure why column G would be negative.

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=186076

Excel Live Chat

Column F = a selectable field to designate the 10-4 or 21-7 shift
choice
Column G = The date reference to begin the "on" versus "off" schedule
Column AZ= Is a repeated formulae that calculates an "on" versus "off"
result. Our schedule calculates several months into the future based
on the formulae.

Thank you once again for your help.

Scott

 

[joel]

I replaced the 2nd part of the IF statement with a lookup for the 2
day time period and then either "ON" or "OFF" to indicate the day type.
I wasn't sure if yo urequired another 2 day period off after every 1
days or just the 1st 2 days off. You should be able to change th
formula to meet your needs.


=IF($G5>0,IF($F5="10-4",IF(MOD(AZ$3-$G5,14)+1<=10,"on","off"),LOOKUP(MOD(AZ$3-$G5,28)+1,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28;"on","on","on","on","on","on","on","on","on","on","off","off","on","on","on","on","on","on","on","on","on","off","off","off","off","off","off","off"})),"")

 

[Scott]

I replaced the 2nd part of the IF statement with a lookup for the 28
day time period and then either "ON" or "OFF" to indicate the day type.
I wasn't sure if yo urequired another 2 day period off after every 10
days or just the 1st 2 days off.  You should be able to change the
formula to meet your needs.

=IF($G5>0,IF($F5="10-4",IF(MOD(AZ$3-$G5,14)+1<=10,"on","off"),LOOKUP(MOD(AZ­$3-$G5,28)+1,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,2­4,25,26,27,28;"on","on","on","on","on","on","on","on","on","on","off","off"­,"on","on","on","on","on","on","on","on","on","off","off","off","off","off"­,"off","off"})),"")

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=186076

Excel Live Chat

Joel... You are a GOD!!!! Thank you very much for your help. It works
perfectly!

Scott