LinkBack URL
About LinkBacks
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
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
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), dilipandey@gmail.com
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.
Ok.. Got it
Use the below array formula:-
Formula:
{=IFERROR(IF(B2-MAX(IF($A1:$A$2=$A2,$C1:$C$2,""))>1,"Gap in Coverage",""),"")}
See the attachment:- Excel book 10.xlsx
Regards,
DILIPandey
<click on below 'star' if this helps>
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
Last edited by leslieharris; 08-11-2012 at 09:47 AM.
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>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks