Calculate hours shared by two intervals


Hi All,

I am having trouble writting a formula to calculate hours shared by two intervals; the basic formula I have been working with are:

Duration: MOD(END-START;1)

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

Case: Worker's Shift: Start=18:00; End=08:00 (next day); first 8 hours of the shift are "regular" (regular_start=18:00, regular_end=02:00)

--Specifically, I need to know how many hours of the "regular" portion  are within the Period-of-Interest: Lower: 19:00; Upper: 06:00 (next day)

(*) This formula works fine when Period-Of-Interest-Upper > Period-Of-Interest-Lower.

Your help will be greatly appreciated.

Kinds regards, DMurray3




First off, you need to write all your times as Date/Time values, including the times for shift-start and shift-end. In your worksheet you probably want to show the regular time start as 18:00 and end as 02:00. Therefore the problem is how to suppress the date value which you have included in your question as "next day" but must be captured by Excel as Date/Time value. You can do this with cell formatting and a helper cell of column, perhaps, where you keep the current date. It's another question, if you need help with it.

For your immediate question, you need the employee's [Start-Work-Time] and the shift's [Shift-End-Time]. Obviousdly, the overlap is

MIN([Shift-End-Time], [Work-End-Time]) - [Start-Work-Time] 
The result is a decimal fraction which you can convert to hours by multiplying with 24.


Thnak you Variatus for your time and response; Unfortunately, I am forced to work with "times", although of course I do have the "date" that corresponds to each shift / case.

I understand how to have the date&time together (..I think it would something like INT(date)+mod(time;1)..), and then via "formatting" show the user the 'times' only.

However, I don't understand your recommended formula; As was included in my file, one of the cases:
Interval A: WorkStarts = 01/08/2017 18:00; WorkEnds = 02/08/2017 08:00
Regular Shift = 8hrs + 1hr Meal Break = 9hrs, so:

Interval 1: RegShiftStarts= 01/08/2017 18:00; RegShiftEnds= 02/08/017 03:00 (ie. 8+1 hours latter)
Interval 2: Period of Interest NGHT: POI_Lower = 01/08/2017 19:00; POI_Upper= 02/08/2017 06:00

The formula I requiere is to calculate the number of hours shared bewteen Interval 1= RegularShift and Interval 2= Period of Interest NGHT, ie: How many hours of the 'RegularShift' are shared with the 'Period of Interest NGHT'.

I apologize if I providing too much information, may have obscured the focal question I was making...

Could you please again evaluate my case and provide new feedback and -of possible- an xlsx file with your suggestions.?

Kind regards, DMurray3
DMurray3 Aug 14, '17 at 10:55 pm
The problem isn't in calculating the time difference or the overlap or any of those things that you explain. It's in the worksheet design, I tried to say. You must design the worksheet in such a way that you have data in in which can be processed. I said they must be Date/Time values. I can help, but I won't desing the sheet for you. Please post the sheet you have and I shall help you correct it. Once the sheet is designed efficiently doing the actual calculations will be easy for anyone. Most probably you won't need help with that. :-)
Variatus (rep: 813) Aug 15, '17 at 2:49 am
I would also ask you to explain how you enter the times or date/times in your worksheet. Are you doing it yourself? Or does the sheet have to be "fool-proof"?
Variatus (rep: 813) Aug 15, '17 at 2:51 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login