# 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

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

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!

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

Do not forget to star

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1