Hello,
I am trying to count the number of the additional hours for my Employee in my Shift calendar:
For example in February: an Employee has 5 additional hours in the month. But he has minus hours in January
Note: that the calculation method is the day, and 1 day = 8 hours
I have used the Mod() and the INT() function in the example shown below, is this true or wrong?
<colgroup><col><col><col span="11"><col><col></colgroup><tbody>
</tbody>
B4: =B3-B2
B5: =INT(B4*8)+A6
B6: =IF((A5+B5)<0,MOD((A5+B5)/8,-1)*8,MOD((A5+B5)/8,1)*8)
B7: =INT(B4*8)+A5
- In the event that the employee has incomplete working hours, you must move to the next month.
- In the event that the employee has additional hours, the missing hours shall be completed first and the remainder shall be counted as an additional.
Sorry about my English
I hope the you understand me !
thanks
I am trying to count the number of the additional hours for my Employee in my Shift calendar:
For example in February: an Employee has 5 additional hours in the month. But he has minus hours in January
Note: that the calculation method is the day, and 1 day = 8 hours
I have used the Mod() and the INT() function in the example shown below, is this true or wrong?
A | B | C | D | E | F | G | H | I | J | K | L | M | ||
1 | 12/2019 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | |
2 | 20 | 21 | 19 | 20 | 12 | 19 | 21 | 21 | 20 | 18 | 17 | 10 | Work days per Month | |
3 | 19 | 21 | 19 | 20 | 12 | 21 | 21 | 21 | 19 | 18 | 17 | 10 | Empleey day Worked | |
4 | -1 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | -1 | 0 | 0 | 0 | Days Minus plus | |
5 | -8 | 0 | 0 | 0 | 0 | 16 | 0 | 0 | -8 | 0 | 0 | 0 | Hours Minus plus | |
6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
7 | -8 | -8 | 0 | 0 | 0 | 16 | 16 | 0 | -8 | -8 | 0 | 0 | Hours Minus plus |
<colgroup><col><col><col span="11"><col><col></colgroup><tbody>
</tbody>
B4: =B3-B2
B5: =INT(B4*8)+A6
B6: =IF((A5+B5)<0,MOD((A5+B5)/8,-1)*8,MOD((A5+B5)/8,1)*8)
B7: =INT(B4*8)+A5
- In the event that the employee has incomplete working hours, you must move to the next month.
- In the event that the employee has additional hours, the missing hours shall be completed first and the remainder shall be counted as an additional.
Sorry about my English
I hope the you understand me !
thanks