check if dates fall within a range

**mcinnes01** · 01-28-2011, 06:16 AM

Hi I need to do some data clensing for the new payroll system we have, and basically I have to get all the absense legacy data in to an accurate format. What I am trying to do is check whether any dates fall within the range of any other dates. Im not sure how to do this.

The is employee number which can be used to distinguish the dates and a from and to date. I need a way of checking and flagging if any dates fall within or match any other dates for that person.

I have attached some example data and made sure there are a couple of date clash instances.

**NBVC** · 01-28-2011, 09:03 AM

Can you indicate on your sample which rows would fall into this conditional check?

**mcinnes01** · 01-28-2011, 12:40 PM

Hi basically I want to check if the range of dates in say d4 and e4 dont fall in to the date range in d5 and e5, d6 and e6, d7 and e7 etc and vice versa.

So an example would be d17 and e17 falls in the date range in cells d16 and e16

**NBVC** · 01-28-2011, 12:44 PM

Does this work for you?

=SUMPRODUCT(--($A$2:$A$29=A2),--($D$2:$D$29>=D2),--($E$2:$E$29<=E2))>1

copied down.

TRUE result means another date range has been found for the employee within the date range in that row.