counting specific hours between two times

Please can someone help improve on this forumla. At the moment it works for all times except midnight. I am also sure that there must be a more efficient way of writing it. Basicly we pay single rate between 8.00am and 8.00pm at all other times and on bank holidays and weekdays we pay an enhanced rate. If I can calculate the standard hours then the remainder of the hours are enhanced.

Aim to count how many work hours fall between a standard start time (SHS) and a standard end time (SHE) (currently 8.00am and 8.00pm), that do not fall on a weekend and are not a bank holiday.

Column B = Start time (eg 06:00)
Column C - End time (eg 22:00)

Column D= date (from which day of week is derived)
Column E = Y or N for whether it is a bank holiday or not.

Current forumla in column P is
=(IF(ISNONTEXT(D11),(IF(OR(WEEKDAY(D11)=1,WEEKDAY(D11)=7,E11="y"),0,IF(B11<shs,IF(C11<=shs,0,MIN(she,C11)-shs),0)+IF(AND(B11>=shs,B11<she),(O11/24)-IF(AND(C11>B11,C11>she),C11-she,0)-IF(C11<B11,MIN(C11,shs)+4/24,0),0)+IF(B11>=she,IF(AND(C11<B11,C11>=shs),MIN(C11,she)-shs,0),0))+A11),0)*24)-R11

Some of our shifts start of end at midnight so I do need the formula to work for that time as well as other times.


Thanks
E

nb for a start time of 6.00am and an end time of 22.00 the formula should calc standard hours of 12

for a start time of 3.00am and end time of 10am the formula should count 2

for a start time of 15:00 and and end time of 24:00 the forumla should count 5.