# Calculating # of Overlapping days with several date ranges.

1. ## Calculating # of Overlapping days with several date ranges.

Hello,

I am having difficulty calculating the number of total overlapping days between several date ranges (6 of them to be precise)

I am using Excel 2003 and my data looks like this:

Sent Out (A) - Received (B)
01/06/2010 13:00 - 30/06/2010 13:00
02/06/2010 13:00 - 16/06/2010 13:00
09/06/2010 13:00 - 10/06/2010 13:00
21/06/2010 13:00 - 25/06/2010 9:44
23/06/2010 13:00 - 25/06/2010 10:56
23/06/2010 13:00 - 29/06/2010 13:00

I really can't wrap my head around the idea of calculating overlap (in network days) between six date ranges and was wondering if any of you would have a solution to this problem.

Cheers

Carl  Register To Reply

2. ## Re: Calculating # of Overlapping days with several date ranges.

Hello Carl,

Are the times relevant or do you only care about the dates. What result would you expect for that example, if you are counting days that are covered at least once won't it just be the same as NETWORKDAYS between 1st and 30th of June?  Register To Reply

3. ## Re: Calculating # of Overlapping days with several date ranges.

let's just focus on the dates. I know that the June 1 - June 30 encompasses all the other dates in this case, but the data will change as users input new dates. Therfore, the formula would need to accomodate all possible combinations of dates (assuming that sent out is always greater than received.)

the whole point in doing this is that I will sum up the business days between all of the six previously mentioned dates and subtract the total quantity of overlapping days to know how long an item is in and out of my hands over the length of a proccess.  Register To Reply

4. ## Re: Calculating # of Overlapping days with several date ranges.

Assuming you have start dates/times in A2:A7 and end dates/times in B2:B7 this formula will count the number of different business days (Monday to Friday).

=SUM(IF(MMULT((WEEKDAY(ROW(INDIRECT(MIN(INT(A2:A7))&":"&MAX(INT(B2:B7)))),2)<6)*(ROW(INDIRECT(MIN( INT(A2:A7))&":"&MAX(INT(B2:B7))))>=TRANSPOSE(INT(A2:A7)))*(ROW(INDIRECT(MIN(INT(A2:A7))&":"&MAX(INT(B2:B7))))<=TRANSPOSE(B2:B7))+0,ROW(A2:A7)^0),1))

This is an array formula that needs to be confirmed with CTRL+SHIFT+ENTER  Register To Reply

5. ## Re: Calculating # of Overlapping days with several date ranges.

Thanks! the formula works very well. Now I wonder if it's possible to only have some of the cells filled out (eg 4 date ranges instead of all the 6) If I remove a date range I get #REF!. Also, if I were to use time in the calculations, would I only need to change the "WEEKDAY" to NETWORKDAYS().

Once more, thanks for your help, I greatly appreciate!  Register To Reply

6. ## Re: Calculating # of Overlapping days with several date ranges.

anyone have any ideas?  Register To Reply

7. ## Re: Calculating # of Overlapping days with several date ranges.

If you use the times how are you expecting that to work? Do you want to count hours only within a specific period or for 24 hours a day during work days?  Register To Reply

8. ## Re: Calculating # of Overlapping days with several date ranges.

measuring by 24 hours a day is what I am seeking, also I get #Ref when some of the cells are empty (eg. only 4/6 actions are done). Thanks!  Register To Reply

9. ## Re: Calculating # of Overlapping days with several date ranges.

Can someone explain this formula to me? Why would I use MMULT? Also, if I had a different date format i.e. 1/12/10 instead of the example in this thread would I have to use the INT function?

=SUM(IF(MMULT((WEEKDAY(ROW(INDIRECT(MIN(INT(A2:A7))&":"&MAX(INT(B2:B7)))),2)<6)*(ROW(INDIRECT(MIN( INT(A2:A7))&":"&MAX(INT(B2:B7))))>=TRANSPOSE(INT(A2:A7)))*(ROW(INDIRECT(MIN(INT(A2:A7))&":"&MAX(INT(B2:B7))))<=TRANSPOSE(B2:B7))+0,ROW(A2:A7)^0),1))  Register To Reply

10. ## Re: Calculating # of Overlapping days with several date ranges.

Hello tvnsf,

Please can you start your own thread with an explanation of exactly what data you have and what you want to achieve, preferably with a small example - there are probably better ways to achieve what you want rather than using the solution here  Register To Reply

11. ## Re: Calculating # of Overlapping days with several date ranges.

Your formula to calculate the number of days excluding overlapping days is outstanding. I've used the same formula and it worked great. However, I just need one more thing in addition that is to add a condition.

I've minimum 2 or maximum 11 date ranges for each item and in another sheet i have unique items where i need the same formula but the condition is for each item. Please reply on the same thread as I'm very new member of this forum just registered and doesn't know how to give reference to this post and ceate a new thread. As my question is already answered in this post what i need more is a condition.  Register To Reply

##### Users Browsing this Thread

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