nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Need Gov‑Safe Method for Detecting Resource Conflicts (Date Overlap + Name Match Failing)

Angelina_Hopkins

I'm working in Smartsheet Gov, which has several limitations that don’t apply in commercial Smartsheet (ex: no Manage References, no HAS(), no FIND() in COLLECT, and very strict parser rules).

I’m trying to build a “Calendar Conflict” flag in my Project Plan sheet that detects when:

A task’s Start/End dates overlap with a blackout entry
Any person assigned to the task appears in the blackout’s “Impacted Person” list
Both conditions must be true to return “Conflict”

Blackout/PTO Calendar sheet has:

Impacted Person (multi-contact)
Start (Date)
End (Date)
Conflict Helper Column (TEXT → a JOIN of the contacts into one plain-text string)

Project Plan sheet has:

Assigned To (contact)
Additional Resources (multi-contact)
All Resources Text (TEXT helper that merges Assigned + Additional)
Date Conflict People (TEXT helper showing blackout overlaps)
Calendar Conflict (desired final output)

Cross-sheet references:

{Blackout Conflict Helper}
{Blackout/PTO Calendar Range 2} = Start
{Blackout/PTO Calendar Range 3} = End

Here is the core issue:
Smartsheet Gov keeps returning #UNPARSEABLE whenever I try to use CONTAINS(), even when the arguments are plain text and outside of COLLECT or COUNTIFS.
Even simple formulas like:

=[All Resources Text]@row + ""

return #UNPARSEABLE, which suggests Gov is blocking contact→text conversions or treating the column reference as invalid.

So far I’ve tried:

Using CONTAINS inside COLLECT (Gov rejects)
Using CONTAINS inside COUNTIFS (Gov rejects)
Converting contacts to text with +"" (Gov rejects)
Building helper columns and separating logic (Gov still errors)
Checking column names for leading/trailing spaces

At this point, I’m looking for any formula pattern that works in Smartsheet Gov, given these constraints:

Requirements:

Must detect date overlap (this part works — COLLECT returns correct rows)
Must detect name overlap between task resources and blackout names
Must avoid HAS(), FIND(), and any text functions inside COLLECT
Must avoid Gov‑blocked contact→text coercion
Must return “Conflict” without #UNPARSEABLE

Has anyone successfully solved person matching + date matching in Smartsheet Gov?
If yes, I’d love to see the pattern that works.

Thank you!

Find more posts tagged with

Sheets

Formulas

functionality

Project Management

Cell linking

Accepted answers

Angelina_Hopkins

THAT WORKED!

All comments

Yulichka

This is what I found on AI.

This is a known, deeply frustrating issue specific to Smartsheet Gov. The #UNPARSEABLE error you are hitting when trying to use + "" or passing contact data into text functions is due to Smartsheet Gov's strict security isolated environment. It completely blocks implicit type coercion (forcing a Contact cell to behave like a Text cell) and chokes on multi-contact evaluation inside array-processing functions like COLLECT() or COUNTIFS().

Since HAS() is missing and CONTAINS() causes parser failures on contacts, we have to flip the logic entirely. Instead of pulling data from the Blackout sheet into the Project Plan to evaluate it, we must use a math-based cross-sheet index matrix that forces Smartsheet to evaluate the logic on the Blackout sheet (where the data is already plain text) and pass back a clean numeric count.

Here is the exact formula pattern that bypasses the Smartsheet Gov parser restrictions.

The Solution Strategy

Instead of trying to convert or parse the multi-contact fields on your Project Plan sheet, we will leave them as contacts. We will use a COUNTIFS matrix mapping back to the Blackout sheet.

Because your Blackout sheet already has a Conflict Helper Column (which is a plain-text JOIN string of the names), we can use the Project Plan's Contact column as a criteria directly inside a cross-sheet COUNTIFS. Smartsheet Gov will allow a single contact cell to act as a text search criteria against an existing text column on another sheet.

Step 1: Update your Project Plan Helper Columns

Because Smartsheet Gov rejects multi-contact parsing, we cannot easily combine Assigned To and Additional Resources into one string without triggering #UNPARSEABLE. We must evaluate them individually and sum the results.

Ensure your Project Plan has these two clean contact columns:

Assigned To (Single or Multi-Contact)
Additional Resources (Multi-Contact)

Step 2: The Final "Calendar Conflict" Formula

In your Calendar Conflict column on the Project Plan sheet, use the following nested IF/COUNTIFS structure.

Code snippet

=IF(
  OR(
    COUNTIFS(
      {Blackout Conflict Helper}, CONTAINS([Assigned To]@row , @cell ),
      {Blackout/PTO Calendar Range 2}, <=[End Date]@row,
      {Blackout/PTO Calendar Range 3}, >=[Start Date]@row
    ) > 0,
    IF(
      NOT(ISBLANK([Additional Resources]@row)),
      COUNTIFS(
        {Blackout Conflict Helper}, CONTAINS([Additional Resources]@row , @cell ),
        {Blackout/PTO Calendar Range 2}, <=[End Date]@row,
        {Blackout/PTO Calendar Range 3}, >=[Start Date]@row
      ) > 0,
      FALSE
    )
  ),
  "Conflict",
  "No Conflict"
)

Why This Pattern Bypasses the Gov Restrictions

1. Reversing the `CONTAINS` Arguments

In commercial Smartsheet, you usually write CONTAINS(substring, cell). In Smartsheet Gov, when dealing with cross-sheet contacts, you must use the inverted criteria syntax: CONTAINS([Assigned To]@row , @cell ) applied against the {Blackout Conflict Helper} range.

This tells the engine: "Look at the text string in the Blackout sheet (@cell@cell ), and see if it contains the contact string from my current row." This avoids the #UNPARSEABLE error because it treats your contact row as a static literal search string rather than trying to transform an entire array.

2. Math-Based Date Overlap Over `COLLECT`

Instead of using COLLECT to find rows and then matching names, standard boolean logic rules dictate that two date ranges overlap if:

$$\text{Start A} \le \text{End B} \quad \text{AND} \quad \text{End A} \ge \text{Start B}$$

By putting this directly into the COUNTIFS, we completely eliminate the need for COLLECT(), which Smartsheet Gov's strict parser frequently breaks on when mixed with text-matching criteria.

3. Isolation of Multi-Contacts

By splitting the evaluation into an OR() statement (one for Assigned To and one for Additional Resources), we bypass the need to merge contacts via + "". If either count returns greater than 0, a conflict is safely flagged.

Angelina_Hopkins

THAT WORKED!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.