Need help with formulas using health status and dates



I'm trying to do a formula in the "Health" column. Once we are within 30 days from the due date, the status turns "Yellow" and once it is within 7 days of the due date and past it, the status would be "Red". Any other status would be green. I'm at a lost to where even to begin even after viewing some of the videos. If you've seen this before or have experience can you point me in the right direction?




  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 10/27/20

    Hi Allison,

    I made an assumption that your sheet also had a column (which I called Status) that would indicate if the task was Complete. Make sure you update this formula to your actual column name and edit my word 'Completed' to your actual wording. Note the wording is case sensitive.

    Add this formula to your Health column. The ball will turn green if the Status is Complete, or if the Due Date is 30 d or more greater than Today. The ball will turn Red if the Due Date is within 7 d or less than today. Anything in between will turn to yellow.

    =IF(OR(Status@row = "Completed", [Due Date]@row - TODAY() >= 30), "Green", IF([Due Date]@row - TODAY() <= 7, "Red", "Yellow"))

    I hope this helps


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!