IF with multiple conditions

Options
Magda
Magda ✭✭✭

Hi!

I am trying to create a formula with the following conditions:

  • I add a "Green symbol" if the "Due date" is in the future and in the "Open/Closed" I add the "Open" or "Closed" Status
  • If "Due date" is 10 days from today and "Open", I get the "yellow symbol". If "Closed" then "Green" shows
  • If "Due date" is in the past and "Open" , then the "red symbol" shows

So far I have this:

=IF(AND(ISBLANK([Due date]@row), [Open/Closed]@row = "Open", " ", IF(TODAY() = [Due date]@row, "Red", IF(TODAY() > [Due date]@row, "Red", IF(TODAY(+10) = [Due date]@row, "Yellow", "Green")))))

Unfortunately, this is not working. Could you please help me?

Thank you!


Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“
    Options

    Hi @Magda,

    This should do what you're after:

    =IF(AND([Due Date]@row = "", [Open/Closed]@row = ""), "", IF(AND([Due Date]@row >= TODAY(10), [Open/Closed]@row = "Open"), "Yellow", IF(AND([Due Date]@row <= TODAY(), [Open/Closed]@row = "Open"), "Red", "Green")))

    Sample data:

    Hope this helps, if there any issues then just post! πŸ™‚

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“
    Options

    Hi @Magda,

    This should do what you're after:

    =IF(AND([Due Date]@row = "", [Open/Closed]@row = ""), "", IF(AND([Due Date]@row >= TODAY(10), [Open/Closed]@row = "Open"), "Yellow", IF(AND([Due Date]@row <= TODAY(), [Open/Closed]@row = "Open"), "Red", "Green")))

    Sample data:

    Hope this helps, if there any issues then just post! πŸ™‚

  • Magda
    Magda ✭✭✭
    Options

    Thank you so much @Nick Korna!

    It worked! πŸ˜€

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!