Need help with IF formula

Hello!

I want to use an RGB Symbol formula that first looks at a 'Completed' cell and if "checked" returns green, but if not checked it moves on to look at a "Date" column to return RED, YELLOW, BLUE or Upcoming. Here is my current formula:

=IF(OR(COMPLETED@row = 1, "Green")), IF(DATE@row < TODAY(), "Red", IF(WEEKNUMBER(DATE@row) = WEEKNUMBER(TODAY()), "Yellow", IF(WEEKNUMBER(DATE@row) = WEEKNUMBER(TODAY()) + 1, "Blue", "Upcoming"))))

Find more posts tagged with

Formulas

Accepted answers

Jeff Reisman

@EC Mac

Not sure why you thought you needed OR in there. You just need a series of nested IFs, where each is the negative condition of the IF before it:

=IF(COMPLETED@row = 1, "Green", IF(DATE@row < TODAY(), "Red", IF(WEEKNUMBER(DATE@row) = WEEKNUMBER(TODAY()), "Yellow", IF(WEEKNUMBER(DATE@row) = WEEKNUMBER(TODAY()) + 1, "Blue", "Upcoming"))))

Sameer Karkhanis

Typically, a completed task is marked Blue and an upcoming or in-progress task (to be completed in future) is marked Green, so going with this logic try this,

Also please rename your DATE column to something like "Target Date" or "Finish Date" etc. as DATE is also a function name and can be confusing.

=IF(Completed@row, "Blue", IF(WEEKNUMBER([Finish Date]@row) < WEEKNUMBER(TODAY()), "Red", IF(WEEKNUMBER([Finish Date]@row) = WEEKNUMBER(TODAY()), "Yellow", IF(WEEKNUMBER([Finish Date]@row) > WEEKNUMBER(TODAY()), "Green"))))

Jeff Reisman

https://community.smartsheet.com/discussion/comment/324233#Comment_324233

Glad it helped!

If you could mark my answer as Accepted, that would be great!

All comments

Jeff Reisman

@EC Mac

Not sure why you thought you needed OR in there. You just need a series of nested IFs, where each is the negative condition of the IF before it:

=IF(COMPLETED@row = 1, "Green", IF(DATE@row < TODAY(), "Red", IF(WEEKNUMBER(DATE@row) = WEEKNUMBER(TODAY()), "Yellow", IF(WEEKNUMBER(DATE@row) = WEEKNUMBER(TODAY()) + 1, "Blue", "Upcoming"))))

Sameer Karkhanis

Typically, a completed task is marked Blue and an upcoming or in-progress task (to be completed in future) is marked Green, so going with this logic try this,

Also please rename your DATE column to something like "Target Date" or "Finish Date" etc. as DATE is also a function name and can be confusing.

EC Mac

Jeff! That was it... thanks so much. I thought I needed the OR to create a Smartsheet 'ELSE' (I know doesn't actually exist). Thanks again!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/324233#Comment_324233

Glad it helped!

If you could mark my answer as Accepted, that would be great!

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.