IF statement using AND to generate colored symbol

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

Tags:

• ✭✭✭✭✭✭
edited 07/28/21
Options

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