Adding hours worked after a specific shift start time?

Hi Everyone

Absolute newb at Excel but im giving it a crack. I am trying to improve my work roster by adding in coloums to calculate costs for each employee.

Where im struggling is the adding up hours worked on different days when start times change, as after 10am a allowance is paid.

If a shift starts "after"10:00hrs it attracts a 15% allowance. Column ER

If a shift has any hours worked between 22:00hrs to 06:00hrs it attracts a further 5% allowance.Column ES

Chasing assistance on adding hours worked on a particular days where the start time commences after 10:00hrs and counting any hours that are worked between 22:00hrs and 06:00hrs, during a night shift.

Not sure what to do with OT hours as we work compressed hours being 8 x 8.5hr days, 1 x 8hrs day with an RDO which makes up our 76hrs for the fortnight.

I currently am working on Row 5 with the formulas.

Any suggestions or help would be greatly appreciated.

Kind regards
Geoff0001

I wouldn't even know where to start looking for an answer, that is one complex spreadsheet you have created. The attachment may have the answers you need or may be able to point you in the right direction.

It's a sheet that contains formulas relating to shift work along with a few examples.

It may help if you could tell us the number values that you would place in ER5:ES5 if you were doing this by hand. Please include an explanation of why those numbers would be placed there given the data in columns F:EK (I assume the information to be used is in row 5). If we know the expected numbers and why they are expected we may have a better chance of producing formulas/code that can replicate the numbers.
Let us know if you have any questions.