IF statement using AND to generate colored symbol

Elizabeth Smiley · 07/27/21

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

Bassam Khalil · 07/28/21

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.

hmiller22 · 12/08/23

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.

IF statement using AND to generate colored symbol

Answers

Help Article Resources

Categories