IF(AND) Formula

Nathan Umbriac

Hello,

I am trying to write a formula to look at a due date field and compare it to a status field.

If the due date is less than or equal to 90 days from today, the status symbol is green. If the due date is less than or equal to 60 days from today and the status is open, the status symbol is yellow. If the due date is less than or equal to 30 days from today and or the status is open, the status symbol is red.

=IF(AND([Due Date]@row <= TODAY(-30), Status@row <> "Closed", "Red", IF(AND([Due Date]@row <= TODAY(-60), Status@row <> "Closed", "Yellow", IF([Due Date]@row <= TODAY(-90), "Green")))))

Thanks!

Jeff Reisman

This may just be misplaced parentheses. You need to close off each AND statement. Try this:

=IF(AND([Due Date]@row <= TODAY(-30), Status@row <> "Closed"), "Red", IF(AND([Due Date]@row <= TODAY(-60), Status@row <> "Closed"), "Yellow", IF([Due Date]@row <= TODAY(-90), "Green")))

Nathan Umbriac

Thanks @Jeff Reisman . That worked, but I decided to update the last part of the above.

If the due date for that row is <=30 days from today and the status is ‘open’, the status ball will be red;

If the due date for that row is <=60 days from today and the status is ‘open’, the status ball will be yellow;

If the due date for that row is <=90 days from today OR the status is ‘closed’, the status ball be green;

Thanks again for your help!

Jeff Reisman

Cool, glad I could help.

Nathan Umbriac

@Jeff Reisman

Would the final part of the IF statement include an OR?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/314831#Comment_314831

I would think it would have to include OR.