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.
Answers
-
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.
-
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"
-
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.
-
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"
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!