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($G50,IF($F5="10-4",IF(MOD(AZ$3-$G5,14)+1<=10,"on","off"),IF(MOD(AZ
$3-$G5,28)+1<=21,"on","off")),"")

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/sh...d.php?t=186076

Excel Live Chat

On Mar 9, 4:01*pm, joel wrote:
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/sh...d.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

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($G50,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","o n","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/sh...d.php?t=186076

Excel Live Chat

On Mar 10, 2:53*pm, joel wrote:
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($G50,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/sh...d.php?t=186076

Excel Live Chat

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

Scott