Nested IF/AND Help with RYGB Status Balls

edited 12/09/19

I'm trying to accomplish two things:

First, for the "Status Visual" column, I'm trying to state the following formula:

• If the "Task Finish" is 3 days overdue, and the "Task Status" is not "Complete" the ball should be red.
• If the "Task Finish" is 1 day overdue, and the "Task Status" is not "Complete" the ball should be yellow
• If the "Task Finish" is a date in the future, and does not meet the above criteria, and "Task Status" is "in progress" it should be "Green"
• If the "Task Status" is "Complete", ball should be blue.
• If it does not meet the above criteria, it should be blank.

Second, for the parent line (row 1 Status Visual Column):

• Looking at the following hierarchy:
• If If any of it's children are red, it should be red.
• If there are no red, but there are children that are yellow, it should be yellow.
• If there are no red or yellow, it should be green, if the project is "In Progress"

• ✭✭✭✭✭✭
edited 03/11/19

Hi Tiffany

I'm just going to focus on phase 1 above!

The overdue phrases you used are specific to 1 day overdue and 3 days overdue.  This formula reflects that exactly, you will need to play with > and < signs instead of the = sign with the Today() functions if you 3 days or more overdue or between 1 and 3 days overdue.  etc  At the moment with your statements there is nothing for 2 days overdue...

Have a play and let me know if this works for you.

Kind regards

Debbie Sawyer Consultant & Training Manager

Smarter Processes mean happy people in successful businesses

• ✭✭✭✭✭✭

.

.

=IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN([Task Status]@row), "In Progress") >0, "Green")))

.

.

See the two formulas above. I was a little confused by what you were looking for with your second formula's "Green" criteria. Is it...

.

If there are no reds or green and the parent's status is "In Progress"

or

If there are no reds or greens, but at least one of the children is in progress (this is how the formula is written)

.

??

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!