Timesheet - ST, OT & DT calculation

[Meucci98]

[TABLE="width: 900"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]DATE[/TD]
[TD]NAME[/TD]
[TD]START[/TD]
[TD]BREAKS[/TD]
[TD]FINISH[/TD]
[TD]TOTAL HRS[/TD]
[TD]ST HRS[/TD]
[TD]OT HRS[/TD]
[TD]DT HRS[/TD]
[/TR]
[TR]
[TD]Fri Nov 17, 2017[/TD]
[TD]John[/TD]
[TD]8:00[/TD]
[TD]0:30[/TD]
[TD]20:30[/TD]
[TD]12:00[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Sat Nov 18, 2017[/TD]
[TD]John[/TD]
[TD]8:00[/TD]
[TD]0:30[/TD]
[TD]16:30[/TD]
[TD]8:00[/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

This is a timesheet for union labour, The union agreement states that:

Monday - Friday workers get paid the first 8 hrs at ST, then 2 at OT and everything after that is DT
Saturday & Sunday workers get paid DT

I am looking for a for a formulas for G, H & I such that the date in A is checked first, if Mon - Fri then total hrs in F are split as per G3:I3. If date is Sat or Sun then total hrs in F are DT

Format for columns C:F is time format
Format for columns G:I is number format

Any help would be greatly appreciated

Thanks in advance

 

[mrshl9898]

I don't know about time format for G:I, but that's your call. Below gives in hours (number format)

G2: =IF(OR(WEEKDAY(A2)=7,WEEKDAY(A2)=1),0,IF(F2>8,8,F2))

H2:=IF(OR(WEEKDAY(A2)=7,WEEKDAY(A2)=1),0,IF(AND(F2>8,F2<=10),F2-8,IF(AND(F2>8,F2>10),2,)))

I2:=IF(OR(WEEKDAY(A2)=7,WEEKDAY(A2)=1),F2,IF(F2>10,F2-10,))