Formula for identifying schedule conflicts with multiple assigned people
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!
Answers
-
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:
- 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. - Modify the Formula to Check for Conflicts for Multiple Names:Use a modified
COUNTIFS
formula that checks each individual in theRESOURCE GROUP
cell for conflicts against theNo 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
orSEARCH
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!
Murphy Carlson
DigitalRadius, Smartsheet Platinum Partner
mcarlson@digitalradius.com
- 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.,
-
Thank you for the quick response! I was hoping to not create separate columns as this defeats the usefulness of multiple people assigned to a task but also do not want some complex process of exporting/importing text for this to work.
-
Hi!
I'd suggest using the HAS function - HAS works great with multi-select columns (contact list or drop down). :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!