Nested IF/AND Help with RYGB Status Balls

Tiffany Blankenship

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"

Debbie Sawyer

Hi Tiffany

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

=IF([Task Status]@row = "Complete","Blue", IF(AND([Task Status]@row = "In Progress",[Task Finish]@row >TODAY()),"Green", IF(AND([Task Status]@row <>"Complete",[Task Finish]@row = TODAY(-1)),"Yellow",IF(AND([Task Status]@row <> "Complete",[Task Finish]@row = TODAY(-3),"Red",""))))

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

 

Paul Newcome

=IF([Task Status]@row = "Complete", "Blue", IF([Task Finish]@row >= Today(), "Green", IF([Task Finish]@row < TODAY(-3), "Yellow", IF([Task Finish]@row >= TODAY(-3), "Red", ""))))

.

.

=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)

.

??