Checking for conflicting assignment dates across two columns
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.
Help Article Resources
Check out the Formula Handbook template!