Forum Discussion
Calculating overtime and double time
Good day. I am struggling with a part of my personal time sheet, and hope that some one can help me out with it.
I am using the office suite that is accessible to me on my hotmail account.
| Time in | Time out | hours worked | regular | O.T. | Double Time | standard | o.t. | ||
| 03:20:00 PM | 03:20:00 AM | 12:00 | 8:00 | 4:00 | 1:00 | 8:00 | 3:00 | 11:00 | |
| 05:50:00 AM | 05:50:00 PM | 12:00 | 8:00 | 4:00 | 1:00 |
|
A2 and B2 are simple time in and time out.
C2 is total hours worked and D2 is regular hours worked. I have this part figured out. My problems arrive when I get to E2, where I kind of have it, but, the total in E2 should not be more than 3 hours, and, anything over that 3 hours should go into F2.
C2 and C3 formulas {=IF(B55<A55,B55+1,B55)-A55}
D2 and D3 formulas {=IF(C55>$K$55,$K$55,C55)} where K55 = 8:00
E2 and E3 formulas {=MAX(L55,IF(C55>D55,(C55-D55)))} where L55 = 3:00
F2 and F3 formulas {=IF(C55>$M$55,C55-$M$55)} where M55 = 11:00
*The {curly brackets} are not in the actual formulas.
Thank you for any help.
It could be
C2: =MOD(B2-A2,1) E2: =IF(C2 > D2, MIN( C2 - D2, 3/24), 0 ) F2: =IF( C2 > (D2 + 3/24), C2 - D2 - 3/24, 0 )
It could be
C2: =MOD(B2-A2,1) E2: =IF(C2 > D2, MIN( C2 - D2, 3/24), 0 ) F2: =IF( C2 > (D2 + 3/24), C2 - D2 - 3/24, 0 )Edited: I think I have it. I just put 8/24 in place of the $K$55.
Works like a charm. Thank you for your time on this.
If I could though, ask one more question on this.
Is there a way on cell D2 to have it max at 8 hrs without having to refer to cell K55?You are welcome.
I'm not sure what do you have in C55, L55, etc. As for D2, max of 8 and what ? Perhaps you may give bit more details.
