IF statement using AND to generate colored symbol
Hello!
Can somebody please help with the following formula? I am trying to generate the colored symbols (Green,Yellow,Red) based on the percentage of tasks complete along with the go live date. I am able to get a single If statement to generate the symbol but am failing at the AND one. Below is the formula that I'm using along with the conditions for the different colors.
Green: If Percentage Complete is greater than 60% and the event end date is less than 30 days
Yellow: If Percentage Complete is greater between 30- 60% and the event end date is less than 30 days
Red: If Percentage Complete is less than 30% and the event end date is less than 30 days
=IF(AND([Percentage Complete]@row >= 0.6, [Event End Date]@row < TODAY(+30)), "Green", IF(AND([Percentage Complete]@row = <0.3 < 0.6, [Event End Date]@row < TODAY(+30)), "Yellow"),IF(AND([Percentage Complete]@row<.3, [Event End Date]@row< TODAY(+30)) "Red"))
Answers
-
Hi @Elizabeth Smiley
Hope you are fine, please try the following formula and convert it to column format formula:
=IFERROR(IF(OR(ISBLANK([Percentage Complete]@row), ISBLANK([Event End Date]@row)), "", IF(AND([Percentage Complete]@row > 0.6, [Event End Date]@row < TODAY(30)), "Green", IF(AND([Percentage Complete]@row < 0.6, [Percentage Complete]@row >= 0.3, [Event End Date]@row < TODAY(30)), "Yellow", IF(AND([Percentage Complete]@row < 0.3, [Event End Date]@row < TODAY(30)), "Red")))), "")
The following screenshot shows the result:
But i think you need to recheck your conditions because let me say you have event date on 1-Oct-2021 if you but any Percentage Complete you will get Blank.
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"
-
When I change number of days out to trigger the color change, the formula returns a blank cell... Not sure what to do. I replaced the 30s with 3s. I have also tried +3.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.7K Get Help
- 63 Global Discussions
- 69 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!