Using multiple IF(AND) statements in a formula

AdamsR · 2023-08-21T21:09:42+00:00

There was an error rendering this rich post.

Hello, I am trying to create a column formula that will update Schedule health to a red, yellow, green, or blue circle using multiple IF and IF(AND) statements, but I continuously get the error message "#INCORRECT ARGUMENT SET"

Here is my formula:

=IF(IF(AND([End Date]@row - [Today's Date]@row <= 0, Status@row = "In Progress"), "Red"), IF(AND((15 >= [End Date]@row - [Today's Date]@row <= 30), Status@row = "In Progress"), "Yellow"), IF(AND(([End Date]@row - [Today's Date]@row >= 30), Status@row = "In Progress"), "Green"), IF(Status@row = "Complete", "Blue", " "))

Here is a screen shot of the columns and the formula in smartsheet:

I am not sure what I am doing wrong, and I've been fiddling with this formula for a few days now. Can anyone help me with this issue? Do I need to separate conditions further? for ex:

Instead of: IF(AND((15 >= [End Date]@row - [Today's Date]@row <= 30), Status@row = "In Progress"), "Yellow")

Do : IF(AND([End Date]@row - [Today's Date]@row <= 30, [End Date]@row - [Today's Date]@row <=15, Status@row = "In Progress"), "Yellow")

Any help would be greatly appreciated

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Give this a try:

=IF(Status@row = "Complete", "Blue", IF(Status@row = "In Progress", IF([End Date]@row< TODAY(), "Red", IF([End Date]@row< TODAY(30), "Yellow", "Green")))

All comments

Paul Newcome

Happy to help. 👍️

AdamsR

Paul, Thank you SO much! That worked perfectly, I was definitely over complicating my formula. I appreciate your expedient response!

Paul Newcome

Give this a try:

=IF(Status@row = "Complete", "Blue", IF(Status@row = "In Progress", IF([End Date]@row< TODAY(), "Red", IF([End Date]@row< TODAY(30), "Yellow", "Green")))

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.