# Sheet Formula_ Heath symbol

Options

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

Tags:

• ✭✭✭✭
Options

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?

Director of Technology & Innovation 💡 at Streamline

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

• Options

@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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!