SOLVED

Calculating overtime and double time

Occasional Contributor

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 inTime outhours workedregularO.T.Double Time standardo.t. 
03:20:00 PM03:20:00 AM12:008:004:001:00 8:003:0011:00
05:50:00 AM05:50:00 PM12:008:004:001: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.

6 Replies
best response confirmed by BrianN68 (Occasional Contributor)
Solution

@BrianN68 

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?

@BrianN68 

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.

@Sergei Baklan.
Sorry for the confusion on that. I thought that I changed it all to row 2.
The B55, C55 etc is where I had these cells in my spreadsheet.
For D2, that would be my straight time. To show 8 hours, the next 3 hours go to E2, then, anything over 11 hours goes to double time.
But, with your help, I have it working now.
Thank you.

@BrianN68 , you are welcome.

 

So, it works now and there are no open questions?

@Sergei Baklan
Yes, it is working good now.
Thank you for your time.