# Flag duplicates with conflicting dates

✭✭

Hi,

I have a list of names with a starting date and a finishing date. I am trying to get a formula that flags conflicting dates for the same person (duplicate). On excel I sorted with a SUMPRODUCT that I know smartsheet does not support. I have also tried to break it down so it adjust to smartsheet but I can't solve the problem of comparing 1 cell to a whole column.

Can anyone think of a solution?

Thank you

Tags:

• ✭✭✭✭✭✭

My suggestion would be more along the lines of...

=IF(COUNTIFS(Person:Person, @cell = Person@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, 1)

This will flag any row where there is date overlap for the person. Applying it as a column formula should do the trick for you.

• ✭✭✭✭
edited 08/09/21

Hi @Marcos,

Without knowing the names of the columns you're using, this is the syntax that should give you the result you're looking for:

=IF(AND(COUNTIF(StartDate:StartDate, StartDate@row) > 1, COUNTIF(Person:Person, Person@row) > 1), 1)

• ✭✭✭✭✭✭

My suggestion would be more along the lines of...

=IF(COUNTIFS(Person:Person, @cell = Person@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, 1)

This will flag any row where there is date overlap for the person. Applying it as a column formula should do the trick for you.

• ✭✭✭✭
edited 08/09/21

No question - @Paul Newcome's suggestion is the way to go here. Nice formula!

• ✭✭✭✭✭✭

@John Pudar Thanks. I came up with this the hard way some time ago and have found it to be very reliable. It also allows you to flag for date overlap where the same exact dates may not necessarily be entered. So if I have a start of 5 August and an end of 9 August on one task, but another task that has a start of 6 August and an end of 10 August, if I am looking for an exact match on dates, this overlap won't get flagged whereas my formula will catch the actual overlap even though the dates don't match.

• ✭✭

Thank you very much @Paul Newcome . That is exactly what I needed!!

• ✭✭✭✭✭✭
• This was a very helpful article! I tried using the formula provided below but I am not sure how I adjust that to fit the plan that I have. I am also trying to flag tasks with conflicting dates for same person. Any idea how I insert that formula?

=IF(COUNTIFS(Person:Person, @cell = Person@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, 1)

• ✭✭✭✭✭✭

@Mallory B You would jsut need to change the column names in the formula to match the column names in your sheet.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!