Is there a way to use Automation to update a Duration column?

CGermain

I have a helper column currently that checks to see if a task is required or not, and I want to set my duration to 0d if it is not required. Right now, I am doing this manually as I can't see a way to get automation to update a Duration column using the "Change a Cell Value" option. Is there a way to do this via Automation?

Paul Newcome

Not through automations. Your choices other than manual are the API, the premium add-on Bridge, or some other similar third party source.

=Chris Palmer

Hello @CGermain,

One option you can explore is to create a new formula column referencing the helper column you created. Without seeing the data you're working with here is an example based on your description.

Create a new formula column with the following:
=IF([Helper Column]@row = 0, "0d", [Duration]@row)

Create an automation rule that updates the Duration column based on the value from the helper formula column.

1. Go to Automation
2. Create a New Rule
3. Set the Trigger: "When a row is changed" or "When a cell is updated".
   You can specify the formula column as the trigger.
4. Set the Action: Choose "Change a cell value" and set the Duration column to be equal to the value of the helper column (the formula column).

This should update the Duration to 0d automatically when the task is not required, based on the formula result.

Hope this helps!

CGermain

https://community.smartsheet.com/discussion/comment/462610#Comment_462610

Hi Chris,

Thanks for the reply, the issue I run into is when I try to select a column in the "Change Cell Value" action, it doesn't give me Duration as an option (only the helper column I created):

=Chris Palmer

We can explore this further, although it may not be pretty since we will add another column! 🙃

You can call it something like "Final Duration"
which will be the actual column you're trying to update automatically.

Set up an automation rule that copies the value from the Helper Duration Column to the Final Duration Column.

• Trigger: "When a row is changed"
  (or any other suitable trigger like "When a cell is updated" on the Formula Column).
  
• Action: "Change Cell Value" where the Final Duration column gets the value from the Helper Duration Column.

For example, when the Formula Column checkbox is unchecked, and the helper column has calculated 0d, the automation will push 0d into the Final Duration column.

If you don't want to clutter the sheet with helper columns, you can choose to hide the helper and intermediary columns after you’ve set up the automation.

In summary:

• Helper Duration Column: This calculates whether a task is required and returns either 0d or the actual duration.
• Final Duration Column: This column is the one you’ll automate to update using the helper column.
• Automation Rule: This rule copies the value from the Helper Duration Column to the Final Duration Column whenever there's a change in the Required column.

The cons of this approach are if the original Duration column is required for different views it may not be entirely accurate since the "Final Duration" column is the new source of truth.

I hope this works, in any sense it's worth experimenting.
Sometimes you can learn something else along the way even if the outcome isn't ideal.

Paul Newcome

The issue is that you are using the built in Duration column linked to the Dependency settings. You cannot update this column via formula or automation. Any column that can be updated will not be accounted for in your dependency settings and will not be used when setting predecessors.

CGermain

https://community.smartsheet.com/discussion/comment/462898#Comment_462898

Thanks Paul, I felt that this was the case.

Paul Newcome

You can use the API, the premium add-on Bridge, or other similar third party apps though. It is just the built in automations and formulas that won't work.