Number of hours between specific times Monday to Friday with exclusions- Timesheet

Hi all,

I have been browsing this forum and haven't been able to find a previously solved solution to this specific problem (apologies if has been done.. couldn't find it!!).

I am essentially trying to set up a spreadsheet that will interpret a pay award correctly. I am having a bit of difficulty working with days/dates & times.

I need to calculate the number of rostered hours between 10pm to midnight, and midnight to 6am Monday to Friday on normal hours only (the first 12 hours of shift excluding breaks, overtime & public holidays) ie: if working after midnight on a Friday, Sat rates are paid INSTEAD of unsociable hours allowance.

Unsociable hours allowance are to be paid:
- On normal hours Monday to Friday (Column H - 1st 12 hours of shift, not overtime or pub hol)
Between 10pm - Midnight &
Between Midnight - 6am
They are paid at different rates hence needing the hours separate.

Are NOT to be paid
- On breaks
- Sat or Sun
- On overtime (O/T)
- On Public holidays

Ideally the formula will reference these times from a cell so they can be changed for a different award (R5:S6)
I'm thinking we may need a helper column - a column that has a formula that returns the time range the first 12 hours fall into??? Which falls well outside of my capability!


Please attached example spreadsheet - I have put the answers required in the pink cells (K15:L19)

Thankyou for your help in advance

Christine

Please try at
K15
=(WEEKDAY(A15,2)<6)*24*(MAX(0,MIN($S$5+($R$5>$S$5),MIN(MOD(C15-B15,1),1/2)+B15)-$R$5)+MAX(0,MIN(MIN($S$5+($R$5>$S$5),E15+MIN(MOD(F15-E15,1),1/2-MOD(C15-B15,1))))-$R$5

L15
=(WEEKDAY(A15,2)<6)*24*(MAX(0,MIN($S$6+($R$5>$S$5),MIN(MOD(C15-B15,1),1/2)+B15)-($R$6+($R$5>$S$5)))+MAX(0,MIN($S$6+($R$5>$S$5),E15+MIN(MOD(F15-E15,1),1/2-MOD(C15-B15,1)))-($R$6+($R$5>$S$5))))

K19 On 20-sep first 12 hours end at 9:40pm No hour fall in 10pm-12am

Bo_Ry's formulas yield the same values that are manually placed in K15:L15 (1 and 0).
There is a difference between what the formula in K19 yields (0) and what is manually placed there (1.75). It may help if we could have an explanation of why 1.75 is expected.
Let us know if you have any questions.