Date Range Comparison only for cells with the same value?

D.J. Rothgeb · 01/28/20

Hello, can any one assist me with building a formula that will do flag a date range overlap for any cells that have the same resources allotted to them? I am basically trying to come up with a work around for not having admin rights nor the ability to assign non-human resources. Example of what I am trying to use except ONLY compare date ranges for cells that have the same inputs in the "assigned to" column:

The code I found to do the initial comparison and flag any date overlap is here:

=IF(OR(ISBLANK(Start@row), ISBLANK(Finish@row)), "", IF(COUNTIFS(Finish:Finish, >=Start@row, Start:Start, <=Finish@row) - 1 > 0, 1))

From this thread: https://community.smartsheet.com/discussion/65257/date-range-conflict-vacation-request-sheet

But I need help to modify this so it only compares cells with like values in the assigned to column.

Thank you!

Paul Newcome · 01/29/20

You are on the right track with the CONTAINS function (so long as the [Assigned To] column is not a contact type column), but it would go in the COUNTIFS instead.

Basically you want to COUNTIFS all rows that have the already established overlap ranges/criteria as well as the range/criteria of the match in the [Assigned To] column.

Once we get that number, we drop it into the IF statement to say that if that number is greater than 1 (there will always be at least one since the row the formula is sitting on matches the criteria), then we want to flag it.

So the COUNTIFS that is already established for date overlap...

COUNTIFS(Finish:Finish, >=Start@row, Start:Start, <=Finish@row)

Add in the additional range/criteria for the [Assigned To] column...

COUNTIFS(Finish:Finish, >=Start@row, Start:Start, <=Finish@row, [Assigned To]:[Assigned To], CONTAINS([Assigned To]@row, @cell))

Then drop that into our IF statement to say that if the result of the COUNTIFS is greater than 1, flag it...

IF(COUNTIFS(Finish:Finish, >=Start@row, Start:Start, <=Finish@row, [Assigned To]:[Assigned To], CONTAINS([Assigned To]@row, @cell)) > 1, 1)

Then add the rest of the formula...

=IF(OR(ISBLANK(Start@row), ISBLANK(Finish@row)), "", IF(COUNTIFS(Finish:Finish, >=Start@row, Start:Start, <=Finish@row, [Assigned To]:[Assigned To], CONTAINS([Assigned To]@row, @cell)) > 1, 1))

D.J. Rothgeb · 01/29/20

I've been trying to figure something out, not having much luck. I tried a couple of contains or has functions but can't get it to return anything.

=IF(OR(ISBLANK(Start@row), ISBLANK(Finish@row)), "", AND(CONTAINS(Assigned@row, Assigned:Assigned), IF(COUNTIFS(Finish:Finish, >=Start@row, Start:Start, <=Finish@row)) - 1 > 0, 1))

Paul Newcome · 01/29/20