How to combine these?

Need the formula to say:

If Completed - Green
If Canceled - Gray
If In Progress - Yellow
If Not Started - Red
If On Hold - Yellow
If In Progress, Not Started, or On Hold and and today is greater than End@row - Red

Here is what I have.... Works fine. Now need to add in the last bullet and I'm stumped.

=IF(Status@row = "Completed", "Green", IF(Status@row = "Canceled", "Gray", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Not Started", "Red", IF(Status@row = "On Hold", "Yellow"

Find more posts tagged with

Formulas

Accepted answers

All comments

Gia Thinh

Hi bobloblaw,

I suggest to add End@row checking into your formula with AND function as below:

=IF(Status@row = "Completed", "Green", IF(Status@row = "Canceled", "Gray", IF(AND(Status@row = "In Progress", End@row>=TODAY()), "Yellow", IF(AND(Status@row = "Not Started", End@row>=TODAY()), "Red", IF(AND(Status@row = "On Hold", End@row>=TODAY()), "Yellow", IF(AND(OR(Status@row = "In Progress", Status@row = "Not Started", Status@row = "On Hold" ), End@row<TODAY()),"Red")))

Try it!

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.