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
Hi Daniel
The formula is quite long but you can give it a try.
I have modified your formula in column H as I need to get the fraction portion of the time else the formula will not work.
Hope this helps.
Sunny
Many ThanksSunnyKow...
I will run your recommended formulae against some real data, just to confirm it doesn't break down the line.
Will revert any findings.
In appreciation,
Daniel Murray
