Formula for identifying schedule conflicts with multiple assigned people

Gregory Jones

I have been trying with no success to create a formula to identify schedule conflicts if multiple people are assigned. The formula works fine with a single person assigned. For the test case, I am using a separate table (on the left) to identify when people will not be available. The right side is representative of typical columns in a schedule sheet.

The formula is as follows currently:

IF(COUNTIFS(NAME1:NAME3, [RESOURCE GROUP]1, [NF START DATE]1:[NF START DATE]3, <=FINISH1, [NF FINISH DATE]1:[NF FINISH DATE]3, >=START1), "CONFLICT", "CLEAR")

When a single person is assigned in [RESOURCE GROUP]1 the formula in [NO FLY]1 behaves correctly

When multiple people are assigned in [RESOURCE GROUP]1, the formula behaves incorrectly.

Any help would be greatly appreciated!

mcarlson

When multiple people are assigned in a single cell, Smartsheet treats the cell content as a single text string, which means the formula you provided won’t recognize each individual as a separate entry. To identify conflicts for each person, we need to split out the names and then check each person’s availability independently. Here’s an approach you can try:

1. Split Names in Separate Columns (Optional): If feasible, it’s easier to have separate columns for each assigned person in the resource group (e.g., Resource 1, Resource 2, Resource 3). If this isn’t possible, you’ll need to use a workaround by searching for each name within the single cell.
2. Modify the Formula to Check for Conflicts for Multiple Names:Use a modified COUNTIFS formula that checks each individual in the RESOURCE GROUP cell for conflicts against the No Fly table.

Solution 1: Separate Columns for Each Person in Resource Group

If you can split each person in the RESOURCE GROUP cell into separate columns (e.g., Person 1, Person 2), here’s how you’d adjust the formula to check each name individually.

excelCopy code=IF(    OR(        COUNTIFS(NAME1:NAME3, [Person 1]@row, [NF START DATE]1:[NF START DATE]3, "<=" & [Finish Date]@row, [NF FINISH DATE]1:[NF FINISH DATE]3, ">=" & [Start Date]@row) > 0,        COUNTIFS(NAME1:NAME3, [Person 2]@row, [NF START DATE]1:[NF START DATE]3, "<=" & [Finish Date]@row, [NF FINISH DATE]1:[NF FINISH DATE]3, ">=" & [Start Date]@row) > 0,        COUNTIFS(NAME1:NAME3, [Person 3]@row, [NF START DATE]1:[NF START DATE]3, "<=" & [Finish Date]@row, [NF FINISH DATE]1:[NF FINISH DATE]3, ">=" & [Start Date]@row) > 0    ),     "CONFLICT", "CLEAR")

This formula uses OR to check each column (Person 1, Person 2, Person 3). If any of the names have a conflict, it returns “CONFLICT”; otherwise, it returns “CLEAR.”

Solution 2: Multiple Names in One Cell

If names are in one cell separated by a delimiter (e.g., a comma or semicolon), you can use FIND or SEARCH to look for each name in the cell. However, Smartsheet formulas have limitations for complex text parsing, so a simpler approach may be creating individual columns for names.

For more complex configurations (e.g., a formula that must parse names from a single cell), exporting the data to a tool like Excel, Google Sheets, or using an external automation tool such as Zapier or Power Automate might be more practical.

Let me know if either of these solutions suits your setup or if there are further constraints!