Slot scheduling conflict within a date range

sherryye17

Hello Smartsheet Masters,

I am making a scheduler sheet for equipment slots. The sheet contains these columns: Conflict (Checkbox), Row (Dropdown list), Slot (Dropdown list, multi-select enabled), Start Date, End Date. I would like the Conflict cell to be automatically flagged if any one or more slots have already been reserved for the same date range, in order to prevent double-booking. That is to say, if any one or more value under the Slot column in that row has appeared in any other row in the sheet and the date ranges overlap.

For instance, rows 1 and 4 in the sheet screenshot below need to be flagged since slots 13, 14, 15 are double-booked from 5/15 to 5/19.

I've tried using COUNTIFS in combination with CONTAINS or HAS but nothing worked so far. Not sure how to construct a formula for this. Any help is greatly appreciated!!

markkrebs

This is off the top of my head.... maybe have another sheet that has all the slots in it and it would collect the min start and the max end. then the conflict flag could read that sheet to determine if the start and end fall between those date. The only issue is that you'll need to flag the rows above to indicate the first time a slot was reserved so that only the future ones are flagged. This might be accomplished with a row counter helper column. Interesting scenario that I'd need to put more thought into.