Help with Automating RYG Balls and Health Status

aishmael

Hi, 

I have been reading other posts and trying to create formula to automate the RYG balls in my project plan, but am not having much luck. Can anyone help me with creating a formula for the following conditions - I have also put I built so far for each condition:

1. If Status is Not Started and Start Date is before Today then Health is Green
   1. IF(AND(Status@row = "Not Started", [Start Date]@row > TODAY()), “Green”
2. If Status is Not Started and Start Date is Today or in the past 7 days than Yellow
   1. IF(AND(Status@row = "Not Started", [Start Date]@row = TODAY()), "Yellow",
3. If Status is Not Started and Start Date is more than 7 days in the past than Red
   1. IF(AND(Status@row = "Not Started", [Start Date]@row < TODAY + 7()), "Red",
4. If Status is In Process and End Date is before Today than Green
   1. IF(AND(Status@row = "In Process", [End Date]@row > TODAY()), “Green”
5. If Status is In Process and End Date is Today or in the past 7 days than Yellow
   1. IF(AND(Status@row = "In Process", [End Date]@row = TODAY()), "Yellow",
6. If Status is In Process and End Date is more than 7 days in the past than Red
   1. IF(AND(Status@row = "In Process", [Start Date]@row < TODAY + 7()), "Red",
7. If Status is Complete than Green
   1. IF(Status@row = "Complete"), “Green”
8. If Status is On Hold than Grey
   1. IF(Status@row = "On Hold"), “Grey”
9. If Status is N/A than Blank
   1. IF(Status@row = ""), “Blank”

I also want to apply this formula to only certain cells in the "Health" column, what is the best way to do this?

Thanks for any help I can get!

Amy 

Paul Newcome

=IF(OR(AND(Status@row = "Not Started", [Start Date]@row < TODAY()), AND(Status@row = "In Process", [End Date]@row < TODAY()), Status@row = "Complete"), "Green", IF(OR(AND(Status@row = "Not Started", [Start Date]@row >= TODAY(-7)), AND(Status@row = "In Process", [End Date]@row >= TODAY(-7))), "Yellow", IF(OR(AND(Status@row = "Not Started", [Start Date]@row < TODAY(-7)), AND(Status@row = "In Process", [End Date]@row < TODAY(-7))), "Red", IF(Status@row = "On Hold", "Gray", IF(OR(Status@row = "N/A", ISBLANK(Status@row)), "")))))

 

This is what will give you what you have posted, but I almost feel as if there may be a little miscommunication or misunderstanding on what it is you are looking for.

 

For example... I would think that if the status is "Not started" and the Start Date is before today, you would want Red maybe? As in... The project has not been started yet, but the start date is 1 August and today's date is 3 August.

 

Maybe if you do a screenshot of each scenario and manually enter the colors you want for each one, we might be able to better understand what exactly you are trying to accomplish.

 

That is unless the above formula is in fact what you are looking for, in which case I do apologize for my assumption.