 SOLVED

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

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

# Re: Calculating overtime and double time

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 )``````

# Re: Calculating overtime and double time

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?

# Re: Calculating overtime and double time

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.

# Re: Calculating overtime and double time

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

# Re: Calculating overtime and double time

@BrianN68 , you are welcome.

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

# Re: Calculating overtime and double time

@Sergei Baklan
Yes, it is working good now.

# Re: Calculating overtime and double time

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?