tuyetngapt
New Member
- Joined
- Oct 2, 2013
- Messages
- 5
Mon | Mon | Mon | Tue | Tue | Tue | Wed | Wed | Wed | Thu | Thu | Thu | Fri | Fri | Fri | Sat | Sat | Sat | Sun | Sun | Sun | Mon | Mon | Mon | Tue |
x | x | 2 | x | x | 5 | x | x | 1 | x | x | x | x | x | x | x | x | x | x | x |
<colgroup><col><col span="2"><col><col span="2"><col span="3"><col span="3"><col span="3"><col span="3"><col span="3"><col span="3"><col></colgroup><tbody>
</tbody>
The workers works 2 shift: morning and afternoon, and they sometime work overtime at night (counted by hours).
1>I want to set up the formula to count the working days for the worker to count the normal working days (exclude overtime and Sunday)
{=COUNT(IF((A1:Y1<>"Sun"),A2:Y2,0))/2} (array formula)
The correct answer should be 7.5 but it turns out 5.
2>I count working days in sunday by {=COUNT(IF((A1:Y1="Sun"),A2:Y2,0))/2} ==>the answer is 11.5 while the correct one should be 1.
3>I want to add the overtime working by =SUMIF(A2:Y2,ISNUMBER(A2:Y2))/8 and it does not work also. (the right result should be 8h)
Can anybody help me to fix this??
Thanks alot.