# Status Column Formula

Options
✭✭✭✭

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

• Overachievers Alumni
Options

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.

• ✭✭✭✭✭✭
Options

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"))), "")
```

bassam.khalil2009@gmail.com

☑️ 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"

• ✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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"))), "")
```

bassam.khalil2009@gmail.com

☑️ 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"

• ✭✭✭✭
Options

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"))), "")

• Options

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!