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?
[TABLE="class: grid, width: 751, align: left"]
<colgroup><col><col><col span="11"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12/2019[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] [/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]12[/TD]
[TD]19[/TD]
[TD]21[/TD]
[TD]21[/TD]
[TD]20[/TD]
[TD]18[/TD]
[TD]17[/TD]
[TD]10[/TD]
[TD]Work days per Month[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD]19[/TD]
[TD]21[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]12[/TD]
[TD]21[/TD]
[TD]21[/TD]
[TD]21[/TD]
[TD]19[/TD]
[TD]18[/TD]
[TD]17[/TD]
[TD]10[/TD]
[TD]Empleey day Worked[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD]-1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]-1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Days Minus plus[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] [/TD]
[TD]-8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]16[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]-8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Hours Minus plus[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD] [/TD]
[TD]-8[/TD]
[TD]-8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]16[/TD]
[TD]16[/TD]
[TD]0[/TD]
[TD]-8[/TD]
[TD]-8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Hours Minus plus[/TD]
[/TR]
</tbody>[/TABLE]
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?
[TABLE="class: grid, width: 751, align: left"]
<colgroup><col><col><col span="11"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12/2019[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] [/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]12[/TD]
[TD]19[/TD]
[TD]21[/TD]
[TD]21[/TD]
[TD]20[/TD]
[TD]18[/TD]
[TD]17[/TD]
[TD]10[/TD]
[TD]Work days per Month[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD]19[/TD]
[TD]21[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]12[/TD]
[TD]21[/TD]
[TD]21[/TD]
[TD]21[/TD]
[TD]19[/TD]
[TD]18[/TD]
[TD]17[/TD]
[TD]10[/TD]
[TD]Empleey day Worked[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD]-1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]-1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Days Minus plus[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] [/TD]
[TD]-8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]16[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]-8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Hours Minus plus[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD] [/TD]
[TD]-8[/TD]
[TD]-8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]16[/TD]
[TD]16[/TD]
[TD]0[/TD]
[TD]-8[/TD]
[TD]-8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Hours Minus plus[/TD]
[/TR]
</tbody>[/TABLE]
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