+ Reply to Thread
Results 1 to 4 of 4

Determining the % of overlap in two date ranges

  1. #1
    Registered User
    Join Date
    07-29-2011
    Location
    Charlotte
    MS-Off Ver
    Excel 2010
    Posts
    26

    Unhappy Determining the % of overlap in two date ranges

    I have four date fields and I'm counting business days aging (networkdays) for Aging 1 and Aging 2.
    A B C D E F G H
    1 Start 1 End 1 Aging1 Start 2 End 2 Aging2 overlap aging Overlap %
    2 6/21/11 6/27/11 5 5/27/11 6/26/11 21 (4) (80%)
    3 6/21/11 6/30/11 8 5/27/11 6/06/11 7 (0) (0%)
    4 6/21/11 6/30/11 8 5/27/11 7/04/11 25 (8) (100%)
    5 6/21/11 6/30/11 8 7/05/11 7/06/11 2 (0) (0%)

    I need a formula that returns the ovelap Aging G2 and Overlap % H2. I'm filling this in manually and it's killing me. My main problem is that the fields don't always overlap and as illustrated above can stat and end at various times within each others process.

    Thank you.

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Determining the % of overlap in two date ranges

    Hi and welcome to the board

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!

  3. #3
    Registered User
    Join Date
    07-29-2011
    Location
    Charlotte
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: Determining the % of overlap in two date ranges

    Thanks for the help.

    An example of my date set and desired results are attahced.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Determining the % of overlap in two date ranges

    Seem to be six possibilities:
    Put this in cell H11:
    =MIN(1,IF(OR(B11<D11,E11<A11),0,IF(B11<E11,NETWORKDAYS(D11,B11),IF(D11<A11,NETWORKDAYS(A11,E11), NETWORKDAYS(D11,E11))/NETWORKDAYS(A11,B11))))

    Not sure why your results are negative, but if you need the negative, just negate the above formula.

    Pauley

+ 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