Need help with IF AND statement using symbols

I need help with using symbols with formulas. The column property is set to symbols. My current working formula is:

=IF(TODAY() > [Remedy Deadline]@row, "Red", IF([Finding Status]@row = "Finding is Closed", "Green", "Yellow"))

However, my first IF condition needs to satisfy two conditions:

(1) (TODAY() > [Remedy Deadline]@row AND

(2) [Finding Status] @row = "Finding is Open"

I thought I was using good syntax with the following IF/AND statement:

=IF(AND((TODAY() > [Remedy Deadline]@row , [Finding Status] = “Finding is Open"), "Red", IF([Finding Status]@row = "Finding is Closed", "Green", "Yellow"))

This simply yields the dreaded "unparseable" without much insight as to what is wrong.

Any suggestions?

Find more posts tagged with

Formulas

Accepted answers

KPH

Hi @Rich G

There are three issues with the formula:

There is an extra ( before the TODAY function: IF(AND((TODAY() >
The reference that has been added for [Finding Status] does not include the row identifier, this should be [Finding Status]@row or [Finding Status] followed by a number
The criteria that has been added starts with a smart quote, it should be a standard quote like the one at the end (this usually happens is the text is copied and pasted, see: “Finding is Open"

Try:

=IF(AND(TODAY() > [Remedy Deadline]@row , [Finding Status]@row = "Finding is Open"), "Red", IF([Finding Status]@row = "Finding is Closed", "Green", "Yellow"))

Hope this helps.

All comments

Rich G

Note, my question misssed a closing quotation; my formula has that in there for [Finding Status] = "Finding is Open"

KPH

Hi @Rich G

There are three issues with the formula:

There is an extra ( before the TODAY function: IF(AND((TODAY() >
The reference that has been added for [Finding Status] does not include the row identifier, this should be [Finding Status]@row or [Finding Status] followed by a number
The criteria that has been added starts with a smart quote, it should be a standard quote like the one at the end (this usually happens is the text is copied and pasted, see: “Finding is Open"

Try:

=IF(AND(TODAY() > [Remedy Deadline]@row , [Finding Status]@row = "Finding is Open"), "Red", IF([Finding Status]@row = "Finding is Closed", "Green", "Yellow"))

Hope this helps.

Rich G

That solved the issue; i work with Smartsheet only intermittently and I should have caught those errors. I learned something re: smart quotes vs. standard quotes.

Thank you for your assistance.

KPH

Happy to help @Rich G

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.