+ Reply to Thread
Results 1 to 6 of 6

Identify gaps in dates within a range

  1. #1
    Registered User
    Join Date
    08-01-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2003
    Posts
    12

    Identify gaps in dates within a range

    I am trying to identify gaps in coverage for insurance. Even for 1 day gap, I would like to identify the person in Column D. Please assist with a simple formula - thanks
    Attached Files Attached Files

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Identify gaps in dates within a range

    Hi leslieharris,

    Why there is gap in coverage as you mentioned in D4 and D7...
    I have checked that a complete year cycle is there in every case .. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    08-01-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Identify gaps in dates within a range

    The gaps are between the person's Start date and the closest End date. For example in row Row 4, Joseph's Start date is 7/7/11 & End date is 7/6/12. There is almost a 2 month gap from the previous coverage plan which is in Row 3 with an End date of 5/11/11.

    Hope this makes sense.

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Identify gaps in dates within a range

    Ok.. Got it

    Use the below array formula:-

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See the attachment:- Excel book 10.xlsx

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  5. #5
    Registered User
    Join Date
    08-01-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Identify gaps in dates within a range

    Hi DILIP again. I applied that formula but it did not work, but then I realized my request and worksheet were unclear. I am comparing the dates in the entire column, not just in the previous row. (My worksheet coincidentally had my desired comparative value in the previous row.

    I have included another attachment with an explanation - thanks again
    Attached Files Attached Files
    Last edited by leslieharris; 08-11-2012 at 09:47 AM.

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Identify gaps in dates within a range

    HI leslieharris,

    I checked the workbook and found that sometime your after looking for above dates and sometime below dates... Earlier till in post #4, we have looked only down. Can you check and confirm ? Thanks.

    Also suggest you to freeze upon the criteria so that this time the solution is complete

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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