If Date is between Today and 4 Days from Today = YELLOW

Options

I've been looking at the different threads for an example that I can use for this formula but could not find anything that helps.

What I'm looking for is if the END DATE is less than today, RED. If its greater than today and less than 4 days in the future, YELLOW, otherwise GREEN. I'm getting YELLOW for any date <= TODAY. END DATES for 10/24/22 should be GREEN.

=IF([End Date]4 < TODAY(), "Red", IF([End Date]4 >= TODAY(-4)), "Yellow", "Green")



Answers

  • Sameer K
    Sameer K ✭✭✭✭
    Options

    This may help,

    =IF([End Date]@row < TODAY(), "Red", 
        IF([End Date]@row - TODAY() < 4, "Yellow", "Green")
    )
    


  • Reggie Andaya
    Reggie Andaya ✭✭✭
    edited 10/17/22
    Options

    @Sameer K Thank you so much, That worked.

    edit:

    If I wanted to add an extra step, to say add the color code only if Status3 is not COMPLETE. If STATUS is COMPLETE, then no color code. This didn't work for me.

    IF([End Date]3 < TODAY(), "Red", NOT(Status@row = "Complete"), IF([End Date]3 - TODAY() < 4, NOT(Status@row = "Complete")"Yellow", IF([End Date]3 - TODAY() > 4, NOT(Status@row = "Complete")"Green"), IF(Status@row = "Complete")""))

  • Sameer K
    Sameer K ✭✭✭✭
    edited 10/18/22
    Options

    In that case it will be,

    =IF(Status@row <> "Complete",
        IF([End Date]@row < TODAY(), "Red", 
            IF([End Date]@row - TODAY() <4, "Yellow", "Green")
        )
    )
    

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!