+ Reply to Thread
Results 1 to 11 of 11

Calculating # of Overlapping days with several date ranges.

  1. #1
    Registered User
    Join Date
    06-25-2010
    Location
    Ottawa
    MS-Off Ver
    Excel 2003
    Posts
    5

    Lightbulb 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

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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?
    Audere est facere

  3. #3
    Registered User
    Join Date
    06-25-2010
    Location
    Ottawa
    MS-Off Ver
    Excel 2003
    Posts
    5

    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.
    Last edited by CarlSVM; 06-25-2010 at 11:25 AM.

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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

  5. #5
    Registered User
    Join Date
    06-25-2010
    Location
    Ottawa
    MS-Off Ver
    Excel 2003
    Posts
    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!

  6. #6
    Registered User
    Join Date
    06-25-2010
    Location
    Ottawa
    MS-Off Ver
    Excel 2003
    Posts
    5

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

    anyone have any ideas?

  7. #7
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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?

  8. #8
    Registered User
    Join Date
    06-25-2010
    Location
    Ottawa
    MS-Off Ver
    Excel 2003
    Posts
    5

    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!

  9. #9
    Registered User
    Join Date
    05-07-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2010
    Posts
    3

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

  10. #10
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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

  11. #11
    Registered User
    Join Date
    01-06-2014
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2003
    Posts
    5

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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