Calculation of shift allowance affected by overtime and overnight shifts

Hi all,

I've spent about two days trying to wrap my head around something that I know has to be simpler than I'm trying to make it, but any pointers with this would be hugely appreciated.

I have an Excel sheet where users enter shift start and finish times (normal Excel time format) - for example: A1 might be 18:00 and A2 might be 06:00 for an overnight shift from 6pm to 6am.

All I need to calculate from these times are the number of hours to which an allowance applies, under the following conditions.

• The allowance is paid for all hours worked between 18:00 and 06:00.
• After 10 hours, a shift becomes overtime and no shift allowance is paid.
• Shifts are regularly worked overnight (i.e. past midnight into the following morning)

Example scenarios include:

• 03:00 - 15:00 would pay 3 hours of shift allowance
• 09:00 - 21:00 would pay 1 hour of the allowance (as the shift becomes overtime from 19:00)
• 03:00 - 21:00 would pay 3 hours
• 19:00 to 08:00 would pay 10 hours (as the shift becomes overtime from 05:00 the following morning)
• 22:00 to 10:00 would pay 8 hours

And so on.

I feel like I’ve got most of the pieces of the jigsaw, but I can’t put them together! I’ve got the following formulae working out bits of what I think I need:

Please Login or Register  to view this content.

The above works out the number of hours before 6am and after 6pm respectively (which I can then SUM), and I believe also accounts for overnight shifts. This obviously doesn't include the more-than-10-hours criteria yet.

The larger formula now looks like this:

Please Login or Register  to view this content.

…but this still doesn’t work properly! The sections referring to 1.25 were my attempt to get the shift allowance to stop if the past-midnight shift continues past 06:00, but I don't think it works properly.

I also know I’m probably using *24 and /24 more than I need to, but that’s partly so I’ve got a better grasp of what the formula is doing.

Once this is working, I'm happy using an IF… >10 formula to prevent the total number of hours of shift allowance being more than 10. However, I’m really struggling to find one single formula that will factor in shifts that might start before 6am and finish after 6pm (i.e. 05:00 – 19:00, which should pay one hour), shifts that might go past midnight and possibly past 06:00 the next day, and so on.

Lastly – not to try and complicate things further – there is an optional cell elsewhere, say A3, where a user enters ‘Y’ if the individual takes an unpaid 30 minute break at some point during the shift. This is to be deducted from whatever type of hours are being paid at the end of the shift. For example, if a shift is from 01:00 - 11:00 with the break, it would pay 5 hours with a shift allowance and 4.5 without. If the shift was 13:00 - 23:00, it would pay 5 without the allowance and 4.5 with the allowance. Is there a practical way of doing this, or does this become much more complicated?

Any help would be gratefully appreciated! I suspect I've just gone round in circles and made things too complicated for myself.

Thanks in advance.