Calculation Help Needed

[Greg (codepug]

A company has 4-shifts (ie 1,2,3,4) and each shift is 1-day (24hrs),
and the shifts repeat after
they complete. Shift 1 to 4 is Monday to Thursday, so the following
Shift 1 to 4 is Friday to Monday and this continues on a 4-day cycle.

If an employee works only on shift2, how do you calculate the number
of days
from shift3 until his scheduled shift2.

Thanks Greg

 

[TheHeatons]

A company has 4-shifts (ie 1,2,3,4) and each shift is 1-day (24hrs),
and the shifts repeat after
they complete. Shift 1 to 4 is Monday to Thursday, so the following
Shift 1 to 4 is Friday to Monday and this continues on a 4-day cycle.

If an employee works only on shift2, how do you calculate the number
of days
from shift3 until his scheduled shift2.

Thanks Greg

Greg,
try this

4-starting shift(shift 3) + employee shift (shift 2)

hth

David

 

[Greg (codepug]

Thanks David but Not Working

4-starting shift(shift 3) + employee shift (shift 2) = 3 this is OK

Does not work when the start shift is a lower number than the EmpShift

4-starting shift(shift 1) + employee shift (shift 2) = 5 instead of 1

There must be a way (Maybe using MOD) ?

Thanks Greg

 

[Greg (codepug]

The formula from the Math Group guys. Thanks.

let n = number of days, c = current shift, w = working shift. Then
n = (w - c + 4) % 4;
where % is the modulus operator.


Alternatively, using the C language's ? operator:
n = (w - c >= 0 ? w - c : w - c + 4);

Dave

Greg

 

[Rick Rothstein \(MVP - VB\)]

If you are converting this formula for use in a worksheet formula, you
should be able to leave out the +4 being added to w-c... that type of
addition (adding the modulus value to an expression) is usually used to
convert a negative remainder to a positive one; however, the worksheet MOD
function always generates positive values, so this should work fine in a
worksheet formula...

=MOD(W1-C1,4)

where I used W1 and C1 references for the variables w and c. Note, if you
are using this expression in VB code, the Mod operator in VB can generate
negative values, so the +4 would be needed there.

Rick