# How to calculate overlapping times of multiple ranges

1. ## How to calculate overlapping times of multiple ranges

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  Register To Reply

2. ## Re: How to calculate overlapping times of multiple ranges

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  Register To Reply

3. ## Re: How to calculate overlapping times of multiple ranges

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!  Register To Reply

4. ## Re: How to calculate overlapping times of multiple ranges

Do not forget to star   Register To Reply

5. ## Re: How to calculate overlapping times of multiple ranges

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  Register To Reply