# IF(AND) Formula

✭✭✭✭✭
edited 01/04/22

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!

• ✭✭✭✭✭✭

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

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭

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!

• ✭✭✭✭✭✭

Cool, glad I could help.

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭

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

• ✭✭✭✭✭✭

I would think it would have to include OR.

Regards,

Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!