Calculating Time Overlaps with Multiple Ranges

I was able to figure out, with a few google searches, how to calculate time overlaps between two ranges using a MIN/MAX formula. Now I need to do the same, but for three (or more) ranges. I have searched and searched, but I can't find a solution. I've attached a sample spreadsheet.

In the example, John is working three jobs, job one from 8AM-4PM, job two from 12PM-2PM and job three from 9AM-11AM. There is a total of 4 hours of overlap, 2 hours from job two and 2 hours from job three. How can I get Excel to calculate that for me?

Thanks!

Overlap.xlsx

Hi,

Quick question here. Is job one time is always higher than the two, three jobs?

No. The time vary, and overlap may not happen between all jobs. There could be overlap between all 3, 1&2, 2&3, 1&3, or none.

Got it. Assuming employee won't do three jobs at a time. Try this simple formula. If they does, provide us more time in and time out data.

Will look out.

=(LARGE(C3:H3,2)-(LARGE(C3:H3,3))+(LARGE(C3:H3,4)-(LARGE(C3:H3,5))))

Attachment : Overlap_possible.xlsx