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
Bookmarks