OT Calculation: Calculate hours shared by two intervals|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hi all,

I am have trouble calculating the number of hours shared by two intervals, specifically when the 'period of interest' crosses the day boundry.

The main formula I have been working with is: 

IF(start<end; MIN(end; upper)-MAX(start; lower); MAX(0;upper-start)+MAX(0;end-lower))

where: WorkShift begins = start; Workshift finishes = end; Period of Interest Begins: lower; Period of Interest Finishes: upper 

The above formula DOES consider when the Workshift cross the day boundry, but fails when both Workshift and Period of Interest.

An important issue is that, I am not using dates+times, but rather only 'times', and would prefer to keep working that way, even though it may prove to be tougher...

Enclosed my sample case which expands the definitions and details I am working with, and trust that you can provide guidance as to how to solve my problem.

Many thanks in advance for your time and feedback. 

Kind regards, DMurray3