Nesting IF & AND statements

Hi, I'm trying to populate a Red, Yellow, Green, Blue column based on two row columns: Status and Due Date. I'm having difficulty trying to set the RYGB column based on both column values. In the below, I'm trying so say that where the Due date is less than today and the Status is In progress make it Yellow. but this keeps throwing an error. Is anyone able to point out my mistake?

Many thanks in advance

Andrew

=IF(Status@row = "Complete", "Blue", IF(AND(([Due Date]@row < TODAY()),Status@row = "In Progress", "Yellow")), IF(ISBLANK([Due Date]@row), "Blue", IF([Due Date]@row >= TODAY(), "Green", IF([Due Date]@row >= TODAY() - 3, "Yellow", "Red")))))

Best Answer

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓

    Hey Andrew,

    The AND function should be AND([Due Date]@row < TODAY(), Status@row = "In Progress"). Below is the formula with the correction.

    =IF(Status@row = "Complete", "Blue", IF(AND([Due Date]@row < TODAY(), Status@row = "In Progress"), "Yellow", IF(ISBLANK([Due Date]@row), "Blue", IF([Due Date]@row >= TODAY(), "Green", IF([Due Date]@row >= TODAY() - 3, "Yellow", "Red")))))

Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓

    Hey Andrew,

    The AND function should be AND([Due Date]@row < TODAY(), Status@row = "In Progress"). Below is the formula with the correction.

    =IF(Status@row = "Complete", "Blue", IF(AND([Due Date]@row < TODAY(), Status@row = "In Progress"), "Yellow", IF(ISBLANK([Due Date]@row), "Blue", IF([Due Date]@row >= TODAY(), "Green", IF([Due Date]@row >= TODAY() - 3, "Yellow", "Red")))))

  • Hi Devin,

    Thank you so much for answering and fixing my question. Really appreciate your help.


    Andrew

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!