Formula

Having difficulty with a formula setting task state (Not Started, In Progress, Complete, On Hold) depending upon how the columns on either side are set - Status/RYGB and % Complete.

=IF(AND(Status@row=“B”, [% Complete]@row =1),"Complete”,IF(AND(Status@row= "G”,[% Complete]@row=0), "Not Started”, IF(AND(Status(G,Y,R)@row= “G”, “Y”, “R”, [% Complete]@row < 0),”In Progress",IF(AND([On Hold]@row = 1), "On Hold”)))

Thanks

Find more posts tagged with

Formulas

Accepted answers

Bassam Khalil

Hi @Melitta

Hope you are fine, as i understand that you use for status column BGYR symbols column type if so then try the following formula.

=IF(AND(Status@row = "Blue", [% Complete]@row = 1), "Complete", IF(AND(Status@row = "Green", [% Complete]@row = 0), "Not Started", IF(AND(OR(Status@row = "Green", Status@row = "Yellow", Status@row = "Red"), [% Complete]@row > 0), "In Progress", "On Hold")))

Melitta

Hello @Bassam.M Khalil

Thank you that is very helpful, and gets me a closer to the final solution. It also makes me realize that I left out a piece.

There is another column: On Hold. It's a checkbox. If it's checked, I want the Task State to be "On Hold". Unchecked and it should follow the rest of the formula.

How do I combine them?

=IF([On Hold]@row = 1, "On Hold")

Thanks,

Melitta

All comments

Bassam Khalil

Hi @Melitta

Hope you are fine, as i understand that you use for status column BGYR symbols column type if so then try the following formula.

Melitta

Hello @Bassam.M Khalil

Thank you that is very helpful, and gets me a closer to the final solution. It also makes me realize that I left out a piece.

There is another column: On Hold. It's a checkbox. If it's checked, I want the Task State to be "On Hold". Unchecked and it should follow the rest of the formula.

How do I combine them?

=IF([On Hold]@row = 1, "On Hold")

Thanks,

Melitta

Bassam Khalil

@Melitta

You are welcome and am happy you find it helpful, Please help the Community by marking it as the accepted answer.

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.