Status Column Formula

Hello Team, can you help me with status column formula to automate status column:

Green = "% complete"=0%

Blue = "% complete"= 100%

Yellow = "Finish Date" >Today() but < Today () + 3d

Red = "Finish Date" + 3d

Remaining "Green"


I was trying but unparseable:


=IF([% Complete]@row="100%", "Blue", IF[% Complete]@row="0%", "Green", IF(AND([Finish Date]@row>Today(), [Finish Date]@row<Today()+3d, "Yellow", IF([Finish Date]@row>TODAY()+3d, "Red", "Green"))))


Please let me know if you need any additional Details, Thanks.


Tags:

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    You may be able to just take out the +3d and enter TODAY(3), which will do the same thing.

    However, I am curious about your % Complete row. "0%" is typically only applicable if you're using a text field to update your percentages. I normally see percentages written as 1 (if 100%) or 0.5 (if 50%). So you may also need to change the way you're referring to your % complete.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Rahul Bhandari 

    Hope you are fine, please try the following formula:

    =IFERROR(IF([% Complete]@row = 1, "Blue", IF(AND([Finish Date]@row > TODAY(),
    [Finish Date]@row < TODAY(3)), "Yellow", IF([Finish Date]@row > TODAY(3), "Red", "Green"))), "")
    

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Rahul Bhandari
    Rahul Bhandari ✭✭✭✭

    Thank you so much David & Bassam,

    My Apologies. I have provided incorrect Requirement earlier. that's why I got incorrect result ( attached)

    Updated requirements:


    please let me know if you fit above in formula or any alternative.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Rahul Bhandari

    Please try the following formula:

    =IFERROR(IF([% Complete]@row = 1, "Blue", IF(AND([% Complete]@row < 1,
    [Finish Date]@row > TODAY(3)), "Green", IF(AND([Finish Date]@row >= TODAY(),
    [Finish Date]@row < TODAY(3)), "Yellow", "Red"))), "")
    

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Rahul Bhandari
    Rahul Bhandari ✭✭✭✭

    Hello Bassam,

    we are almost near to success. not getting desired result for red status. for ex. today is 29th sept so for past due tasks for dates 28th, 27th & 26th should be yellow and all other red.

    Formula I used:

    =IFERROR(IF([% Complete]@row = 1, "Blue", IF(AND([% Complete]@row < 1, [Finish Date]@row > TODAY()), "Green", IF(AND([Finish Date]@row < TODAY(1), [Finish Date]@row < TODAY(3)), "Yellow", "Red"))), "")



  • Hey did you manage to find a solution to this?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!