Status Column Formula

Rahul Bhandari

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.

Status Column Formula.PNG

David Tutwiler

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

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

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:

Status Formula_Updated.PNG

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

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

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

Jez Akali

Hey did you manage to find a solution to this?