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