Calculation Question

[Greg (codepug]

Date Dates Days on Group Shifts Total Number of days missed
Sick Gone By SickList Working Passed Group1 Group2 Group3 Group4

1/3/2003 1/3/2003 1.00 1 1 1 0 0 0
1/4/2003 2.00 2 1 1 1 0 0
1/5/2003 3.00 3 1 1 1 1 0
1/6/2003 4.00 4 1 1 1 1 1
1/7/2003 5.00 1 2 2 1 1 1
1/8/2003 6.00 2 2 2 2 1 1
1/9/2003 7.00 3 2 2 2 2 1
1/10/2003 8.00 4 2 2 2 2 2
1/11/2003 9.00 1 3 3 2 2 2
1/12/2003 10.00 2 3 3 3 2 2
1/13/2003 11.00 3 3 3 3 3 2
1/14/2003 12.00 4 3 3 3 3 3
1/15/2003 13.00 1 4 4 3 3 3
1/16/2003 14.00 2 4 4 4 3 3
1/17/2003 15.00 3 4 4 4 4 3
1/18/2003 16.00 4 4 4 4 4 4
1/19/2003 17.00 1 5 5 4 4 4
1/20/2003 18.00 2 5 5 5 4 4
1/21/2003 19.00 3 5 5 5 5 4
1/22/2003 20.00 4 5 5 5 5 5
1/23/2003 21.00 1 6 5 5 5
1/24/2003 22.00 2 6 5 5
1/25/2003 23.00 3 6 5

Formulas for Group Working & Shifts Passed

=(MOD(B6-$A$6,4)+1) ‘Group that is working

=INT(((B6-$A$6)/4) + 1) ‘Shifts that have passed since the sick day

My company has 4-groups (ie 1,2,3,4). Each Group is 24hrs of work and
an individual is assigned to only a single group, so he only works 1-
day out of 4. I need to be able to calculated the number of times an
individual has missed working in his group when he is out sick.

Please help me figure out a formula to use to calculate this in the
Group Columns so the results are as shown in the columns. I want to
avoid using If statements.

 

[Greg (codepug]

Basically, I’m looking to calculate the number of actual work days
missed by an employee. My company has 4-groups (ie 1,2,3,4). Each
Group is 24hrs of work and an individual is assigned to only a single
group, so he only works 1-day out of 4. I need to be able to
calculated the number of times an individual has missed working in his
group when he is out sick.

If an individual is assigned to Group1 and is out of work for 10-days,
and he calls in sick the day he is scheduled to work he will miss 3
actual work days. If he calls in sick the day after he works, and is
out 10-days he will miss 2 actual work days.

I’m ultimately looking for a formula that I can use in my access
database for each record involving a sick individual, but have turned
to the spreadsheet to try to work it out. This is proving difficult,
especially since I hope to avoid using an
If statement for a number of reasons.

Thanks
Greg

 

[daddylonglegs]

Hello Greg,

Assuming your headers "Group1", "Group2" etc. are in F4:I4 then try this
formula in F6 copied down and across:

=COUNTIF($D$6:$D6,SUBSTITUTE(F$4,"Group",""))

Note: if you made the headers just 1, 2, 3 and 4 [custom format as
"Group"0] then you could simplify the formula to:

=COUNTIF($D$6:$D6,F$4)

 

[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

 

[daddylonglegs]

Did I misunderstand? I thought you wanted formulas for the "Group" columns?
In your example some of those values are 5, surely any solution using Modulus
4 will only return values between 0 and 3