asked on

Identifying records with overlapping dates

I have a sample of a file where because of erroneous input, some of the start date and end dates are overlapping. I have identified the two records where the dates are overlapping in this scenario.

However, I have a file with thousands of records where this can happen. Is there a way to program this in VB, formulas , or any way possible to identify these records?
EE-Mutiples.xlsx

redmondb

Hi, morinia.

I don't understand your definition of "overlapping". For example, Row 3 is shown as not overlapping, whereas Row 5 is.

Regards,
Brian.

SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

redmondb

Barry,

I was having a slow day! When I realised that it was from row to row and went to post, Rob had beaten me to it.

Regards,
Brian.

morinia

ASKER

I added an extra condition for checking the member_Id in column A was the same. The sample file I sent only had one member_id.

=IF(A2=A1,IF(B2<=C1,"Y",""),"")

Thanks for your help

barry houdini

Hello morinia,

My suggestion takes the ids into account and also works on sorted or unsorted data - it's going to be a little less efficient though. With your modification of Rob's suggestion that might only identify the first instance of many where there's an overlap, e.g. if you have a period from 7th to 14th December followed by another period of 8th to 9th Dec and then 10th to 11th and 14th to 17th then all of those last 3 periods overlap with the 1st one, but the formula will only identify the second one as an overlap.

regards, barry

morinia

ASKER

I split the points. I had to give points to the first answer because when I posted the example I did not mention there were multiple ID's.