Help with IF, AND, THEN Formulas to change status column

JayF ✭✭
edited 04/26/23 in Formulas and Functions

I am stuck! I've tried multiple variations of this formula but can't seem to get anything to work. I'm looking to have the status column changed automatically based on two other columns; "Date Required" column which is a date and a "No Follow Up Required" column which is a checkbox.

Here's what I'm looking for the formula to do:

If the "No Follow Up Required" checkbox is checked change status to "Complete"

If the "No Follow Up Required" checkbox is unchecked and the "Date Required" date has passed then change status to "Overdue"

Otherwise I want the status to be set to "In Progress"

Here is the formula I've been trying

=IF([No Follow Up Required]@row = 1, "Complete", IF(AND([No Follow Up Required]@row = 0, [Date Required]@row<=TODAY, "Overdue", "In Progress"))

Hoping someone can help me figure this out.

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!