August 12, 2017
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
June 25, 2016