How to calculate overlapping times of multiple ranges

**bgrablin** · 04-09-2013, 12:32 PM

First off, great site. I have solved many problems with just a simple search. But I have hit a brick wall.

Background:
Tracking total downtime of two assets (Asset A & Asset B), where both assets are down. Time is covering a 24 hour period between 2 days (i.e. 3 Apr 12:00 - 4 Apr 12:00).

I've used the following formula for calculating total time between a stated time: MAX(MIN(D1,B1)-MAX(C1,A1)) But this does not allow for calculating total time between a range of times.

I've attached an example with an expected outcome, only without the formula in question.

overlapping times.xlsx

Thanks in advance.

**AZ-XL** · 04-09-2013, 01:09 PM

f3 cell =IFERROR(INDEX($B$3:$B$5,MATCH(1,(D3>$A$3:$A$5)*(D3<$B$3:$B$5),0))-D3,0)+IFERROR(E3-INDEX($A$3:$A$5,MATCH(1,(E3>$A$3:$A$5)*(E3<$B$3:$B$5),0)),0) CSE

Calculates overlapping of start taime and end time

**bgrablin** · 04-09-2013, 04:03 PM

Thank you for your assistance. Formula worked great.

For anybody reading this, Cell F3 would need to be entered as an array (Ctrl+Shift+Enter).

Thanks again!

**AZ-XL** · 04-09-2013, 04:05 PM

You are welcome. Glad that I could help you.

Do not forget to star

**bgrablin** · 04-12-2013, 12:22 PM

Quick question. When I put in a time for Asset B that overlaps on both sides of Asset A, the formula doesn't calculate. Also, I had an issue with times that match, not calculating as well.

Eaxample: overlapping times-issue.xlsx