Automate an "Active" field

Joechaptaylor

Hi,

I have a sheet wherby i have statuses, start and end dates, and also an Active field

I would like the "active" field to populate whenever a status is either

• In Progress
• Review
• Sign Off

And when the date is either Today, or in the past.

I'd like the field to also clear, if none of the above is met.

I have set up the below, but this doesnt seem to work…. any ideas??

Andrée Starå

https://community.smartsheet.com/discussion/123305/automate-an-active-field

Hi,

I hope you're well and safe!

What happens when you test the workflow?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

DKazatsky2

Hi @Joechaptaylor,

Does this have to be an automation? If you are open to using a formula, try this in the "Active" column.

=IF(AND(OR(Status@row = "In Progress", Status@row = "Review", Status@row = "Sign Off"), [Start Date]@row <= TODAY()), 1)

Hope this helps,

Dave

Andrée Starå

https://community.smartsheet.com/discussion/123305/automate-an-active-field

Hi,

I hope you're well and safe!

What happens when you test the workflow?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Joechaptaylor

Hi,

This works perfect using

=IF(AND(OR(Status@row = "In Progress", Status@row = "Review", Status@row = "Sign Off"), [Start Date]@row <= TODAY()), 1)

Is there any way to add an exception that if the Start Date row is blank, it doesnt tick the field?

Andrée Starå

https://community.smartsheet.com/discussion/comment/441004#Comment_441004

Excellent!

Try something like this.

=IF(AND(OR(Status@row = "In Progress", Status@row = "Review", Status@row = "Sign Off"), [Start Date]@row <> "", [Start Date]@row <= TODAY()), 1)

Did that work?

✅ Remember! Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!