Calculating overtime and double time

[BrianN68]

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.

[#2 · Accepted 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 )

[#3]

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?

[#4]

@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.

[#5]

@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.

[#6]

@BrianN68 , you are welcome.

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

[#7]

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

[#8]

I have a question....
I already have a sheet with different fields and my OT column is working, but I need a formula for DT.
Anything over 4 hrs in the G column (OT) needs to go to H column (DT), but G column needs to stay at 4

Start Finish Total Job Description Reg OT DT
8:00 AM 9:45 PM 13.75 At office. 8 4 1.75

C: =(MOD(C12-B12,1)*24)
E: =(D12)-G12
F: =IF((MOD(C12-B12,1)*24)>8,(MOD(C12-B12,1)*24)-8,0)
G: what should this be...?

Or what should I change all my formulas to?