Semi-monthly Timesheet with OT calculating based on daily and weekly limits

[trs43]

I have created a timesheet that shows overtime options. I will try to make a copy (can we upload a copy of our file?) I have created checkbox for daily and weekly overtime options. If Daily (True), OT is calculated over 12 hours, if Weekly (True, OT is also calculated over 48 hours per week). Differential pay is paid on evening shifts only.

Total Hours is calculated as:

=ROUND((IF(OR(D14="",E14=""),0,IF(E14<d14,e14+1-d14,e14-d14))+if(or(g14="",h14=""),0,if(h14<g14,h14+1-g14,h14-g14))+if(or(j14="",k14=""),0,if(k14<j14,k14+1-j14,k14-j14))) 1440)

Regular Hours are calculated as:
=+L14-O14

Overtime Hours are calculated as:
=ROUND(MAX(IF($C$7,MAX(0,SUM(M13:M$13)+L14-$C$8/24),0),IF($C$4,IF(L14>$C$5/24,L14-$C$5/24,0),0))/(1/1440),0)*(1/1440)

Where C7 = Weekly Checkbox True, C8 = 48 hours, C4 Daily Checkbox True, C5 - 12 hours

My problem is that the weekly overtime needs to reset based on the weeknum for the payperiod. Does anyone have any suggestions. I can't seem to wrap my head around this one. I have even thought about a cumulative hours column based on weeknum, but can't seem to get that formula right either.

I would appreciate any assistance you can provide.

A

B

C

D

E

G

H

J

K

L

M

O

P

Q

R

S

T

Weeknum

Date

Shift

Time in

Time Out

Time In

Time Out

Time In

Time Out

Total Hrs

Reg Hrs

OT Hrs

Diff Hrs

Diff OT Hrs

Absent Hrs

Stat Hrs

Vac Hrs

44

Nov 1

Days

6:00 am

12:45 pm

1:10 pm

8:00 pm

13:35

12

1.35

44

Nov 2

Nights

3:00 pm

8:45 pm

9:00 pm

2:30 am

11.25

11.25

11.25

<tbody>
</tbody>

</d14,e14+1-d14,e14-d14))+if(or(g14="",h14=""),0,if(h14<g14,h14+1-g14,h14-g14))+if(or(j14="",k14=""),0,if(k14<j14,k14+1-j14,k14-j14)))>