Time and shift patterns formulas, please

Hi All,

Thanks for reading. I am trying to work out a formula to calculate day and night hours with the following rules:
Day shift = 06:00:00 - 18:00 :00 - regular day rate
Night shift = 18:00:00 - 06:00:00 - regular night rate
Saturday starts at 00:00 (Saturday) and ends at 12:00:00 Saturday when it becomes ... (Saturday morning is enhanced rate)
Sunday until 23:59:00 on Sunday night (Saturday PM until midnight Sunday is double time)

Workers often start early e.g. 05:00:00, overtime is often spread over Friday evening into Saturday morning and sometime even a couple of hours into Saturday afternoon.

The reports I have to work with are formatted as hh:mm:ss hence I have included everything this way

Can anyone help, please ... it's driven me mad for two days now

Thanks

Can you attached any sample file, please?

Not sure about this.

Please try at
E2 Press Ctrl+Shift+Enter

Formula:

=SUM(INDEX(FREQUENCY(ROW(INDIRECT(ROUND((WEEKDAY(B2,3)+MOD(B2,1))*1440,)+1&":"&ROUND((WEEKDAY(C2,3)+MOD(C2,1))*1440,))),{360;1080;1800;2520;3240;3960;4680;5400;6120;6840;7200}),N(IF(1,ROW($A$1:$A$5)*2)))/60*(WEEKDAY(B2,2)<6))

F2 Press Ctrl+Shift+Enter

Formula:

=SUM(INDEX(FREQUENCY(ROW(INDIRECT(ROUND((WEEKDAY(B2,3)+MOD(B2,1))*1440,)+1&":"&ROUND((WEEKDAY(C2,3)+MOD(C2,1))*1440,))),{360;1080;1800;2520;3240;3960;4680;5400;6120;6840;7200}),N(IF(1,ROW($A$1:$A$6)*2-1)))/60*(WEEKDAY(B2,2)<6))

G2
=MAX(0,ROUND((MIN(6.5,WEEKDAY(C2,2)+MOD(C2,1))-MAX(WEEKDAY(B2,2)+MOD(B2,1),6))*24,4))
H2
=MAX(0,ROUND((MIN(8,WEEKDAY(C2,2)+MOD(C2,1))-MAX(WEEKDAY(B2,2)+MOD(B2,1),6.5))*24,4))