Status Flow with If Statements

Michael Jemison

Here is my first IF statement that when the Site Survey task gets populated it puts it into the complete status.

=IF(ISBLANK([Order Sign Date]@row), "", IF(ISBLANK([Site Survey Complete Date]@row), "Pending", "Complete"))

Now that I am able to get the next task ROE in progress with the Site Survey task completed I am trying to make another IF statement so that when the ROE Complete date is populated it puts the task into the "Completed" status and then puts the next task in progress.

=IF([Site Survey Status]@row = "Pending", "", IF([Site Survey Status]@row = "Complete", "Pending", IF(OR(ISBLANK([Right of Entry Completion Date]@row), "Pending", "Complete"))))

When I populate the Right of Entry Completion Date the status remains at Pending. How do I get it to move to Completed?

jmyzk_cloudsmart_jp

Hi @Michael Jemison

You're close! The issue is with how you're using OR — it’s mixing conditions and text, which causes problems.
Try this cleaner formula instead:

=IF([Site Survey Status]@row = "Pending", "", IF(ISBLANK([Right of Entry Completion Date]@row), "Pending", "Complete"))

https://app.smartsheet.com/b/publish?EQBCT=949624e36f714e90bd40da6dbc6aebee

This way, the status will properly move to "Complete" when the ROE date is filled!

jmyzk_cloudsmart_jp

Hi @Michael Jemison

You're close! The issue is with how you're using OR — it’s mixing conditions and text, which causes problems.
Try this cleaner formula instead:

=IF([Site Survey Status]@row = "Pending", "", IF(ISBLANK([Right of Entry Completion Date]@row), "Pending", "Complete"))

https://app.smartsheet.com/b/publish?EQBCT=949624e36f714e90bd40da6dbc6aebee

This way, the status will properly move to "Complete" when the ROE date is filled!

Michael Jemison

@jmyzk_cloudsmart_jp Thank you so much!!

jmyzk_cloudsmart_jp

Happy to help!😁