Adding hours for split shifts

Hi There,

I am struggling to get the formula correct for adding hours on a staff rota.

I have done alot of searching on here and have found a formula that does most of what I need, apart from one calculation which shows as an error.

My staff work spilt shifts - say starting at 12:00-14:00 then 17:00-01:00
I have found a formula that can handle that, but if the staff member only works in the evening say 17:00 - 01:00 then I get an error.

Also when adding the actual hours worked I get an incorrect amount.

I have attached the file with some boxes filled for you to see.

Many Thanks for any assistance in making this work!