Formula for Health Symbols

Could you please help me to create a formula?

There are 3 columns, Start Date, End Date and Completed (this is a check box)

I would like to have the symbols change

if a date is between today and the due date = Green

if the date is 3 days before the due date from today, then turn Yellow

if the date is on or past due date from today = Red

if completed column is checked, then turn Blue

Thank you so much and any help would be greatly appreciated!

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Hi @Marcos Aguilar ,

    Try this:

    =IF(Completed@row = 1, "Blue", IF([end date]@row <= TODAY(),"Red", IF([end date]@row <= TODAY(3), "Yellow", IF([end date]@row > TODAY(3), "Green","")))

    It translates to:

    If the completed box is checked, show blue. Otherwise, if the end date is on or before today, show red. Otherwise, if the end date is on or after 3 days after today, show yellow. Otherwise, if the end date is more than 3 days after today, show green. Otherwise, show blank.


    I hope this helps! Let me know if it works for you.



    Best,

    Heather

  • I totally get where you're coming from, Marcos Aguilar. Working with those health symbols can be a bit of a puzzle sometimes. But don't worry, we've all been there! Your idea sounds super organized, and I'd be thrilled to share my take on this.

    First off, big thanks to you, for that awesome formula suggestion. It's clear and concise. 

    Marcos, I'd suggest giving this formula a whirl:

    less
    
    Copy code
    =IF([@Completed]=TRUE,"Blue",IF([@End Date]<=TODAY(),"Red",IF([@End Date]<=TOD
    

    So, if the Completed box is checked, it's Blue. If the End Date is on or before today, it's Red. If the End Date is within 3 days from today, it's Yellow. Otherwise, it's Green.

    I've used table referencing ([@]) for clarity and compactness. Remember to replace "End Date" and "Completed" with the actual column names.

    Just young healthtech expert in Andersen😍

    Portfolio: https://andersenlab.de/industries/healthcare/telehealth-solutions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!