Set Duration to 0d with automation

We have a project template sheet with default tasks, durations, predecessors, etc. When a user saves the template as a new project they can then decide which tasks are not relevant to that project by marking unneeded tasks as 'N/A' in a Status dropdown list column.

We created a 'Clear Cell Value' automation to clear the Duration field on a task when the Status is changed to 'N/A', however, it does not work (i.e. nothing happens). We do have (and need) Dependency Settings turned on for the project. I looked at changing the automation action to 'Change cell value' but Duration is not available for that time of action.

I've seen other posts similar to this but don't recall seeing any recent updates. I'd be interested to know what workarounds exist as well. Thank you!



  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭

    Hi @Awalshga !

    Here is a workaround for you - it works by using a Move Row automation to get rid of the irrelevant rows and requires:

    1. If your template is not a folder (i.e., just the one Sheet) move it into a folder.
    2. Add a blank Sheet to this folder/ the existing folder if you have one.
    3. Set up a Move Row automation in your Project sheet to send rows to the blank Sheet whenever Status is "N/A".

    Make sure you copy the Folder when creating new projects.

    And as a bonus you could set a similar automation in the destination Sheet to move rows back to the schedule if they were erroneously removed.


    Dan Palenchar | School of Sheets Solutions Consulting (Smartsheet Aligned Gold Partner)

    Smartsheet Consulting Inquiries:

    Smartsheet Tutorial Videos:

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

    PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!

  • @Dan Palenchar thanks for the response and idea. I actually tried your suggestion previously and it worked great. If they accidentally mark it as N/A and want to move it back, do you know of a way to store/preserve the original row number so the user knows where to put it back in their plan? Or would it just go to the bottom like typical Move Row behavior and they would need to put it back wherever it should go?

    It sounds like you can confirm that the automation to clear the Duration field does not work in SS, correct?