Calculating # of Overlapping days with several date ranges.

**CarlSVM** · 06-25-2010, 11:00 AM

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

**daddylonglegs** · 06-25-2010, 11:15 AM

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?

**CarlSVM** · 06-25-2010, 11:20 AM

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.

**daddylonglegs** · 06-25-2010, 11:48 AM

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

**CarlSVM** · 06-25-2010, 01:30 PM

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!

**CarlSVM** · 06-30-2010, 03:11 PM

anyone have any ideas?

**daddylonglegs** · 06-30-2010, 03:28 PM

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?

**CarlSVM** · 07-05-2010, 10:04 AM

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!

**tvnsf** · 05-07-2013, 06:10 PM

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))

**daddylonglegs** · 05-07-2013, 06:24 PM

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

**ZafferAhmed** · 01-06-2014, 07:09 AM

Hello daddylonglegs,

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.

Thanks in advance.

Calculating # of Overlapping days with several date ranges.

LinkBack

Thread Tools

Rate This Thread

Display

Calculating # of Overlapping days with several date ranges.

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions