Nested IF, OR, AND with TODAY formula to return a symbol (RYGB) based on "Drop Down Menu Status"

Options

Hi All,

I'm trying to set a symbol (Red, Yellow, Green, Blue) in a column based on the individual values in one column.

Here is what I have so far (but it is wrong):

=IF([Current Status]@row = "Sarepta", "Green", IF(OR([Current Status]@row = "Thermo", [Current Status]@row = "Catalent"), "Yellow", IF(AND(TODAY()[Current Status]@row = "Current pending", [Review Due Date]@row > 1, "Red", IF(OR([Current Status]@row = "Approved", "Closed", "Blue"))))))


Please help me!

-Aris

Tags:

Answers

  • MVP OPS
    MVP OPS ✭✭✭✭✭
    Options

    Hi @Aris

    Looks like you are missing the condition in your Today section of your AND statement, closing parenthesis for your And statement. You should be able to add your condition and closing parenthesis, and delete one closing parenthesis and be set.

    Current:  IF(AND(TODAY()[Current Status]@row = "Current pending"


    Hope this helps,

    best,

    Brad

    www.MVPOPS.com

  • Aris
    Options

    Hi MVP OPS,


    Thank you it looks to have made the formula somewhat work. I am receiving another error now for "INCORRECT ARGUMENT SET".


    Does this have to do with last part of my formula? Or the AND statement/TODAY argument logic?

    Thanks,

  • MVP OPS
    MVP OPS ✭✭✭✭✭
    Options

    Hi @Aris

    if you can copy and paste your formula as it looks now I can help identify the issue.

    Looking at your original formula the end is missing items as well, it should look like this:

     IF(OR([Current Status]@row = "Approved",[Current Status]@row = "Closed"), "Blue"

    best,

    Brad

    www.MVPOPS.com

  • Aris
    Options

    =IF([Current Status]@row = "Sarepta", "Green", IF(OR([Current Status]@row = "Thermo", [Current Status]@row = "Catalent"), "Yellow", IF(AND([Current Status]@row = "Current pending", TODAY([Review Due Date]@row > 1, "Red", IF(OR([Current Status]@row = "Approved", [Current Status]@row = "Closed"), "Blue"))))))

  • MVP OPS
    MVP OPS ✭✭✭✭✭
    Options

    Thank you try this one:

    =IF([Current Status]@row = "Sarepta", "Green", IF(OR([Current Status]@row = "Thermo", [Current Status]@row = "Catalent"), "Yellow", IF(AND([Current Status]@row = "Current pending", TODAY([Review Due Date]@row) > 1), "Red", IF(OR([Current Status]@row = "Approved", [Current Status]@row = "Closed"), "Blue"

    best,

    Brad

    www.MVPOPS.com

  • Aris
    Options

    Hi Brad,

    It has returned "#INVALID DATA TYPE" for those fields that previously stated "#INCORRECT ARGUMENT SET"

  • MVP OPS
    MVP OPS ✭✭✭✭✭
    Options

    Hi Aris,

    this could be due to the non date input in the review date field. try removing the "closed" from the date field.

    best,

    Brad

    www.MVPOPS.com