Parent Row Schedule Health functionality

Hello,

I have a column formula that is returning random results, mostly on parent rows. The formula is:

=IF(Status@row = "Complete", "Green", IF(OR(Status@row = "In Progress", Status@row = "Not Started", Status@row = "On Hold"), IF(ISBLANK([Target End Date]@row), "Red", IF([Target End Date]@row < TODAY(), "Red", IF([Target End Date]@row <= TODAY() + 7, "Yellow", "Target End Date"))), ""))

Here is the screenshot of the results it is returning, with the rows in question highlighted. What am I missing? Thank you in advance @Paul Newcome @Andrée Starå

Find more posts tagged with

Formulas

parent row

Accepted answers

All comments

Carson Penticuff

Logically, the results match the formula. What would you like the results of the highlighted cells to be?

Paul Newcome

Using the top two rows as an example, your Status is "In Progress" which means that it goes on to evaluate the Target End Date. Since the Target End Date is not blank, is not in the past, and is not in the next 7 days, it is going to the expected output of "Target End Date". What did you want it to do instead?

RSmith30

Thank you @Carson Penticuff and @Paul Newcome for the evaluation. It seems that I am missing a condition that returns a result of green if it is not blank, not in the past, and not in the next 7 days (all is good). What would the formula look like to include that condition?

Secondly, the last highlighted row for "educate production" is showing blank for status health. To clarify, it's because there isn't a value in the status column? If so, I'd like to add a condition that results red for status health if status column is blank also.

Very much appreciated. With all these different conditions, I can get tripped up with correct formatting of the formula!

Paul Newcome

Try this:

=IF(Status@row = "", "Red", IF(Status@row = "Complete", "Green", IF(OR(Status@row = "In Progress", Status@row = "Not Started", Status@row = "On Hold"), IF(ISBLANK([Target End Date]@row), "Red", IF([Target End Date]@row < TODAY(), "Red", IF([Target End Date]@row <= TODAY() + 7, "Yellow", "Green"))), "")))

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.