Nesting IF & AND statements

Andrew Foster

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

Devin Lee

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

Devin Lee

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

Andrew Foster

Hi Devin,

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

Andrew