Need help with auto calculating excess hours worked to apply to "Make-up" column before balance to "Over Time"

[JoyeDigi]

I am trying to get the Timesheet to automatically allot hours worked in excess of 8:00 per day to “Make-up” column to a MAX of 40:00; remaining balance if any should be allocated to OT Hrs.

Please save me from myself and too many )s!

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

Company Name

2

Address

Time Sheet

Std Work Day

8:00

3

Std Work Week

40:00

4

Employee:

SAMPLE

Employee Cell phone:

772-555-5555

5

Position:

Paralegal

Pay period start date:

9/11/2015

6

Start Date:

2/18/2013

Pay period end date:

9/24/2015

7

8

Day

Date

In

Lunch

Return

Out

Hrs Wkd

Reg Hrs

Make-up

OT Hrs

Sick

Vaca

Hol

NOTES

Total

9

Friday

########

8:30 AM

12:00 PM

1:00 PM

6:30 PM

9:00

8:00

1:30

9:30

10

Saturday

########

10:00 AM

12:00 PM

2:00

2:00

2:00

11

Sunday

########

12:00 PM

3:00 PM

3:00

3:00

3:00

12

Monday

########

9:00 AM

12:30 PM

12:30 PM

7:00 PM

10:00

8:00

2:00

10:00

13

Tuesday

########

8:00 AM

12:00 PM

2:30 PM

3:00 PM

4:30

4:30

2:00

6:30

14

Wednesday

########

8:30 AM

12:00 PM

12:00 PM

5:00 PM

8:30

8:00

0:30

8:30

15

Thursday

########

8:30 AM

12:00 PM

1:00 PM

2:30 PM

5:00

5:00

5:00

16

Subtotal

42:00

38:30

1:30

2:30

2:00

44:30

Need Make-up total MAX = 40:00.

i9

=IF(SUM($H$16+O4)<$O$3,(SUM($O$3-$H$16)),$O$4)

i10

=IF(SUM($H$16+I9)<=$O$3,(SUM(G10-H10)),($O$4))

i11

=IF(SUM($H$16+I9+I10)<=$O$3,(SUM(G11-H11)),(SUM($O$4)))

i12

=IF(SUM($H$16+I9+I10+I11)<$O$3,(SUM($O$3-$H$16)),$O$4)

i13

=IF(SUM($H$16+I9+I10+I11+I12)<$O$3,(SUM($O$3-$H$16)),$O$4)

i14

=IF(SUM($H$16+I9+I10+I11+I12+I13)<$O$3,(SUM($O$3-$H$16)),$O$4)

i15

=IF(SUM($H$16+I9+I10+I11+12+I13+I14)<$O$3,(SUM($O$3-$H$16)),$O$4)

<tbody>
</tbody>

 

[JoyeDigi]

I9 needs to return only 1:00; so that :30 returns in J9.
Total of Reg Hrs + Make-up = MAX of 40:00.

 

[JoyeDigi]

I12 should be 0:30, while J12 should = 1:30.