Timesheet - ST, OT & DT calculation

[Meucci98]

A	B	C	D	E	F	G	H	I
DATE	NAME	START	BREAKS	FINISH	TOTAL HRS	ST HRS	OT HRS	DT HRS
Fri Nov 17, 2017	John	8:00	0:30	20:30	12:00	8	2	2
Sat Nov 18, 2017	John	8:00	0:30	16:30	8:00			8

<tbody>
</tbody>

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