IF statement using AND to generate colored symbol

07/27/21 Edited 07/27/21
Answered - Pending Review

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:

Answers

  • Bassam KhalilBassam Khalil ✭✭✭✭✭
    edited 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.

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ 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"

Sign In or Register to comment.