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å
Answers
-
Logically, the results match the formula. What would you like the results of the highlighted cells to be?
-
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?
-
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!
-
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"))), "")))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 430 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 75 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!