# Nesting IF & AND statements

Options

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?

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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

• Options

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!