Count of hours within time range

[Chartley86]

Column D contains a list of time ranges e.g. D1 is 16:00-22:00, i need a formula to return the amount of full, consecutive hours within another time range, this will always be 20:00-06:00. So for the example D1 i would need a return of 2 hours.
Thanks

 

[Chartley86]

Sorry my typing error, i meant when the hours are 16:00 - 00:00 the second formula is giving me -0.8333 instead of 4

 

[Joe4]

This formula is becoming a bit unwieldy to try to handle all the different possible scenarios.
Would you be opposed to using VBA to create our own function in order to do this?

 

[Chartley86]

Im sorry i dont know what VBA is?

 

[Joe4]

Visual Basic for Applications. Sometimes people refer to it as "Macros".

Basically, we can use Visual Basic code to build our own function. The only caveat is that you must allow for VBA to run in your workbook.
If you are subject to some sort of workplace security, you may need to check with them to make sure that they allow for you to use it.