Formula to change the Health status of my projects based on data from two cells

andrews

I'm trying to get a formula that will change the Color dot in my Project health status field, based on conditions in two other cells. What I want is:

If the "Project Status" field = Active or On Hold, AND the "Project Wrap Date" field is Today (7), change the Health Status field to a Yellow dot; or if the "Project Status" field = Active or On Hold, AND the "Project Wrap Date" field = in the past, change the Health Status field to a Red dot; OR if the "Project Status" field = Active or On Hold, AND the "Project Wrap Date" field = today (8), Change the Health Status to a Green Dot, Else Change the Health Status to a Blue Dot.

What I have right now is the fields changing based on the "Wrap Date", but I don't know how to add in the Status condition. That formula is:

=IF([Project Wrap Date]@row <> "", IF([Project Wrap Date]@row < TODAY(), "Red", IF([Project Wrap Date]@row <= TODAY(7), "Yellow", "Green")), "Blue")

Any help would be appreciated.

Thanks

Christian Graf

This formula should include your yellow conditions.

=IF([Project Wrap Date]@row <> "", IF([Project Wrap Date]@row < TODAY(), "Red", IF(AND([Project Wrap Date]@row <= TODAY(7), OR([Project Status]@row = "Active", [Project Status]@row = "On Hold")),"Yellow", "Green")), "Blue")

=IF(AND([Project Wrap Date]@row <= TODAY(7), OR([Project Status]@row = "Active", [Project Status]@row = "On Hold")),"Yellow", "Green")

We use the AND() function to get the today(7) value and Project status to be true together, then we used the OR() function to compare the project statuses if one or the other is true.

Hope this helps. If it doesn't work with the OR function you might have to fully write out each expression.

=IF(AND([Project Wrap Date]@row <= TODAY(7), [Project Status]@row = "Active"),"Yellow", IF(AND([Project Wrap Date]@row <= TODAY(7), [Project Status]@row = "On Hold"),"Yellow", "Green")

Hopefully that can get you on the right track

Christian Graf

This formula should include your yellow conditions.

=IF([Project Wrap Date]@row <> "", IF([Project Wrap Date]@row < TODAY(), "Red", IF(AND([Project Wrap Date]@row <= TODAY(7), OR([Project Status]@row = "Active", [Project Status]@row = "On Hold")),"Yellow", "Green")), "Blue")

=IF(AND([Project Wrap Date]@row <= TODAY(7), OR([Project Status]@row = "Active", [Project Status]@row = "On Hold")),"Yellow", "Green")

We use the AND() function to get the today(7) value and Project status to be true together, then we used the OR() function to compare the project statuses if one or the other is true.

Hope this helps. If it doesn't work with the OR function you might have to fully write out each expression.

=IF(AND([Project Wrap Date]@row <= TODAY(7), [Project Status]@row = "Active"),"Yellow", IF(AND([Project Wrap Date]@row <= TODAY(7), [Project Status]@row = "On Hold"),"Yellow", "Green")

Hopefully that can get you on the right track

andrews

Thanks! That is exactly what I was missing. This works great now.