Checking for conflicting assignment dates across two columns

Kevin T

Long time reader, first time poster--this one has a few people stumped.

I have a variety of events with Start Dates & End Dates. Staff members are assigned as a 1st Guide or 2nd Guide (its critical we differentiate which role they have been assigned).

I am looking for a way to pop a warning symbol when one of the names is assigned to conflicting events. The challenge has been that we need to look for conflicts within "1st Guide", conflicts within "2nd Guide", AND conflicts between the two.

This formula seems to work for checking for conflicts within a single column:

=IF((ISBLANK([1st Guide]@row)), 0, IF(COUNTIFS([1st Guide]:[1st Guide], @cell = [1st Guide]@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, 1, 0))

It's the method for cross-checking the two columns that we can't find a solution for--by formula, helper sheet, whatever might work.

Huge thanks to anyone who can crack this! It will make a lot of people's lives much easier, this will allow us to use the Calendar App directly to schedule.

Jeff M.

I think I solved it for you!!!!!!

I created a column named "1st Guide Flag" and plugged in this formula.

=IF((ISBLANK([1st Guide]@row)), 0, IF(COUNTIFS([1st Guide]:[1st Guide], @cell = [1st Guide]@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, JOIN([1st Guide]@row)))

Then another column named "2nd Guide Flag" with this formula

=IF((ISBLANK([2nd Guide]@row)), 0, IF(COUNTIFS([2nd Guide]:[2nd Guide], @cell = [2nd Guide]@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, JOIN([2nd Guide]@row)))

Then a final column for cross checking "Cross Check Flag"

=IF(COUNTIFS([1st Guide]:[2nd Guide], @cell = [2nd Guide]@row, [Start Date]:A, @cell <= [End Date]@row, [End Date]:C, @cell >= [Start Date]@row) = 1, JOIN([1st Guide]@row:[2nd Guide]@row))

These columns will return the names in conflict.

The warning symbol could be accomplished by setting up conditional formatting that will highlight the 1st Guide or 2nd Guide cell when the corresponding "Flag" column "is not blank".

Jeff M.

Oh yes, I forgot to add that part in my initial response. The reason for having the letters was that I created new helper columns and named them A, B, C. I used them to troubleshoot and correct the error messages that were returning with the cross flag formula. When using the COUNTIFS formula, the range of columns throughout the formula need to be equal. Since the cross check formula was evaluating both 1st guide & 2nd guide columns, I added helper columns that would add to each Date column (e.g. [Start Date]:[Helper Column A] & [End Date]:[Helper Column B])

Also, you'll want to make sure the "helper columns" are directly next to the other date column so the column range is valid. Once the column is added, you can select to hide the helper columns right away.

Jeff M.

I think I solved it for you!!!!!!

I created a column named "1st Guide Flag" and plugged in this formula.

=IF((ISBLANK([1st Guide]@row)), 0, IF(COUNTIFS([1st Guide]:[1st Guide], @cell = [1st Guide]@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, JOIN([1st Guide]@row)))

Then another column named "2nd Guide Flag" with this formula

=IF((ISBLANK([2nd Guide]@row)), 0, IF(COUNTIFS([2nd Guide]:[2nd Guide], @cell = [2nd Guide]@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, JOIN([2nd Guide]@row)))

Then a final column for cross checking "Cross Check Flag"

=IF(COUNTIFS([1st Guide]:[2nd Guide], @cell = [2nd Guide]@row, [Start Date]:A, @cell <= [End Date]@row, [End Date]:C, @cell >= [Start Date]@row) = 1, JOIN([1st Guide]@row:[2nd Guide]@row))

These columns will return the names in conflict.

The warning symbol could be accomplished by setting up conditional formatting that will highlight the 1st Guide or 2nd Guide cell when the corresponding "Flag" column "is not blank".

Kevin T

I think you're onto something here! I'm just not quite not following what "A" and "C" are referring to in the "Cross Check Flag" formula, I think I need to get my head wrapped around that in order to make it work.

... [Start Date]:A, @cell <= [End Date]@row, [End Date]:C...

But this promises to be much more elegant than a single monster formula!

Kevin T

@Jeff M. Oops, forgot to tag you in my response above--reposting here

I think you're onto something here! I'm just not quite not following what "A" and "C" are referring to in the "Cross Check Flag" formula, I think I need to get my head wrapped around that in order to make it work.

... [Start Date]:A, @cell <= [End Date]@row, [End Date]:C...

But this promises to be much more elegant than a single monster formula!

Jeff M.

Oh yes, I forgot to add that part in my initial response. The reason for having the letters was that I created new helper columns and named them A, B, C. I used them to troubleshoot and correct the error messages that were returning with the cross flag formula. When using the COUNTIFS formula, the range of columns throughout the formula need to be equal. Since the cross check formula was evaluating both 1st guide & 2nd guide columns, I added helper columns that would add to each Date column (e.g. [Start Date]:[Helper Column A] & [End Date]:[Helper Column B])

Also, you'll want to make sure the "helper columns" are directly next to the other date column so the column range is valid. Once the column is added, you can select to hide the helper columns right away.

Kevin T

Thank you so much @Jeff M. !! This worked great, and is a much more elegant solution than using a monster formula like we were. And as a bonus, my schedulers can click into the row from the Calendar App if they need to see who the conflict warning is for.