Formula Assistance

CSmall1122 ✭✭
edited 06/21/23 in Formulas and Functions

I am attempting to do the following:

  1. If status is Complete or N/A = Green
  2. If start date is within 5 days or past due and status is not Complete or N/A = Red
  3. If start date is more than 5 days and status is not Complete or N/A = Yellow

I have the following formula, but no luck. Any help is greatly appreciated.

=IF(OR(Status@row = "Complete", Status@row = "N/A"), "Green", IF([Start Date]@row <= TODAY(5), "Red", IF(Status@row <> "Complete", Status@row <> "N/A", "Yellow", "Green")))



  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    If I am understanding this correctly, you can simplify the formula quite a bit.

    =IF(OR(Status@row = "Complete", Status@row = "N/A"), "Green", IF([Start Date]@row <= TODAY(5), "Red", "Yellow"))

  • CSmall1122
    CSmall1122 ✭✭
    edited 06/21/23

    Thanks Carson, this almost gets me where I want to be. For the Yellow, I want it to indicate even on rows without a start date and status is not Complete or N/A. This is where I'm struggling. Any tips on this? @Carson Penticuff

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @CSmall1122,

    This should work for you:

    =IF(OR(Status@row = "Complete", Status@row = "N/A"), "Green", IF(AND(OR([Start Date]@row > TODAY(5), ISBLANK([Start Date]@row)), OR(Status@row <> "Complete", Status@row <> "N/A")), "Yellow", "Red"))

    Hope this helps; if you've any questions or problems with it then just post! 😊

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!