Sheet Formula_ Heath symbol

09/07/21
Answered - Pending Review

Hello everyone,


I know I am close, but looking for some help. I am trying to create a formula to calculate

If start date is less than today, health symbol is Green

If start (maybe should be end date) is within (xx) days, health symbol turns yellow

If start date (maybe should be end date) is greater than today, turns Red

If % of completion is 100%, health symbol turns blue



Examples

=IF([Start Date]@row < TODAY(), "Green", IF(AND([Start Date]@row >= TODAY(), [Start Date]@row < TODAY(+7)), "Yellow", "Red"))


=IF([% Complete]@row, =100, "Blue", IF([Start Date]@row < TODAY(), "Green", IF(AND([Start Date]@row >= TODAY(), [Start Date]@row < TODAY(+7)), "Yellow", "Red")))



Answers

  • It looks to me like you will only have "green" projects if they are prior to starting. Is that correct? Or should you base it off the finish date?


    What is the issue or error you are running into with the formulas you posted?

    Holly Conrad Smith

    Director of Technology & Innovation 💡 at Streamline

    CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony

  • @hollyconradsmith - I believe that is working correctly, however the formula for turning health symbol to blue with % of complete is 100% I am having a hard time figuring out

Sign In or Register to comment.