Time and shift patterns formulas, please

[y_not]

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

[KOKOSEK]

Can you attached any sample file, please?

[y_not]

Think I was attaching as you requested - thanks

[y_not]

Can you attached any sample file, please?

Uploaded. Thanks

[Bo_Ry]

Not sure about this.

Please try at
E2 Press Ctrl+Shift+Enter

Please Login or Register  to view this content.

F2 Press Ctrl+Shift+Enter

Please Login or Register  to view this content.

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