• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

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
0
morinia
Asked:
morinia
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now