Need formula to calculate number of hours in a time span that are between 6PM and 6AM

Our employees work many different shifts. Whatever shift they work, they get night premium for those hours that fall between 6PM and 6AM the next morning. Some shifts start and end before midnight, and other shifts start before midnight and go until after midnight, and some start before midnight and go until after 6AM, and others start at midnight and go until after 6AM. Below is an attachment showing some example shifts. I need a formula that will calculate the number of hours in the shift that fall between 18:00 and 06:00. If possible, I need the same formula to be used whether or not the shift crosses midnight. Thanks in advance for any help.

Test.xlsx

Originally Posted by ConneXionLost

How about:

Please Login or Register  to view this content.

That gives me the total hours. I need it to give me the hours during that span that fall between 6PM and 6AM.

Thanks daddylonglegs. That worked for me.

=MOD(B2-A2,1)*24-(B2<A2)*12-MEDIAN(B2*24,6,18)+MEDIAN(A2*24,6,18)

How would this formula read if you wanted to calculate time span between midnight and 4AM?