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
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

redmondbCommented:
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.
0
Rob HensonFinance AnalystCommented:
Are you able to sort the data differently? By start date would be good!

If sorted you could do a simple date comparison of current start date with previous record finish date.

Once sorted, starting in D2 formula would be:

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

Copy down as required.

Thanks
Rob H
0
barry houdiniCommented:
Hey Brian!

Row 3 doesn't overlap with anything, though, does it?

Try this formula in D2 copied down

=IF(COUNTIFS(A:A,A2,B:B,"<="&C2,C:C,">="&B2)>1,"Y","")

That identifies any row which overlaps with any other, 4 to 9 on the example, see attached

regards, barry
27493462.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

redmondbCommented:
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.
0
moriniaAdvanced Analytics AnalystAuthor Commented:
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
0
barry houdiniCommented:
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
0
moriniaAdvanced Analytics AnalystAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.