Calculate vairable payrates per single shift over midnight - Timesheet

Hi Grant,

I'd suggest to add helper sheet like this

where we have all combinations of hours for this and next day (totally 48) with 1 if an hour for that shift type ans zero otherwise. In addition, numbers for weekend are multiplied proportionally to rated. Here

WDD if this and next days are working ones;

WDE - work day and next weekend;

WED - weekend and next working one;

and it looks like i missed WEE

Result will be like this

I didn't combine all formulas in one (for total). Formula for pay unsocial will be

=(SUMPRODUCT((Scale!$A$11:$A$58>C2*24)*(Scale!$A$11:$A$58<=E2*24)*OFFSET(Scale!$A$10,1,MATCH(B2&" WDD",Scale!$B$10:$M$10,0)+6,48))+
MOD(C2,1/24)*INDEX(OFFSET(Scale!$A$10,1,MATCH(B2&" WDD",Scale!$B$10:$M$10,0)+6,48),C2*24)*24+
MOD(E2,1/24)*INDEX(OFFSET(Scale!$A$10,1,MATCH(B2&" WDD",Scale!$B$10:$M$10,0)+6,48),E2*24)*24)*
IF(B2="Event",Scale!$B$3,Scale!$C$3)

all others are similar ones.

That's just mockup, I didn't fully tested it. Attached.