Manadatory break after 10 days of work formulae
 

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")),"")

Manadatory break after 10 days of work formulae
 

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

Manadatory break after 10 days of work formulae
 

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

Manadatory break after 10 days of work formulae
 

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

Manadatory break after 10 days of work formulae
 

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