Workflow to Populate Actual Start Date When %Complete changes from 0 or blank

Summer M
Summer M
edited 06/10/24 in Formulas and Functions


I currently have the following workflow set in my smartsheet:

What I'm trying to achieve is to record an actual start date on the first date any % complete is recorded for a row and then I have a second workflow that is set to mark an Actual Finish date and lock the row when % complete reaches 100%. I'm having an issue right now, though, where my actual start date is getting overridden when %s in % complete column are changing at all (which makes sense based on this workflow) - but I can't figure out how to make this a one time action. As in, I want my sheet to populate the actual start ONLY when the % complete changes FROM BLANK or FROM ZERO to any value, and then not change again.

Is there a way to make my workflow more specific like this?


  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/10/24

    Add a condition where Actual start date is blank.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Vince Darrigo

    Hi @Summer M

    You could add a checkbox column to your sheet (and hide it if you don't want to see it) let's call it New Checkbox

    Then in the workflow you posted, in conditions add 2 of them:

    "New Checkbox is not checked"

    "% Complete is greater than 0"

    Then, change your action from Record Date, to Change Cell Value, and choose to check New Checkbox.

    From there, create a new workflow that says when rows are changed and New Checkbox changes to Checked, Record Date in Actual Start.

    This way, the automation can only happen one time each time the record's percent complete goes above 0%. Annoying that you cannot update the checkbox and record the date in the same workflow, but that's because Smartsheet considers each of those actions to be workflow terminating actions. I daisy chain things like this to get around that. I hope this helps!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!