Flag duplicates with conflicting dates

Marcos

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

Paul Newcome

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.

John Pudar

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)

Paul Newcome

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.

John Pudar

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

Paul Newcome

@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.

Marcos

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

Paul Newcome

Happy to help. 👍️

Mallory B

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)

Paul Newcome

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