Adding a day to Duration for each day a project is On Hold

PM_Reeves

Hi,

I've inherited 90-ish projects that have Dependencies enabled in the End Date column, so this may make my question moot.

I'd like to add 1 day to the End Date for each day that a project is On Hold.

I've read that I can't add formulas to cells where Dependencies are in place, so I looked at Automation as an option, but I can only figure out an option to increase it for Day 1 of On Hold.

Am I barking up the wrong tree? Is it even possible?

Cheers.

jmyzk_cloudsmart_jp

Hi @PM_Reeves

To address adding a day to the duration of a project that is on hold in Smartsheet, consider a combined approach using helper columns, automation, and the Smartsheet API:

Helper Columns and Formulas: 

Utilize helper columns, such as 'Planned End Date' and 'Actual End Date.' Implement formulas in these columns to adjust the end date based on project status changes​​. However, it's important to note that you can't place formulas in date columns used for dependencies.​😅

Automation Process Idea:

• Add a Checkbox Column: Introduce a checkbox column in your Smartsheet for tasks needing duration adjustment.
• Auto-Formatting for On Hold Status: Set conditional formatting rules to change the color of the checkbox cell when a task's status is set to "On Hold."
• Update Request Workflow Automation: Create a workflow that triggers update requests when the checkboxes are checked. This automation enables you to bulk-update the duration for tasks marked as on hold.

Smartsheet API for Automated Updates: As a final solution, use the Smartsheet API. Demo Python script automatically adds a day to the duration of tasks marked as "On Hold."😀

https://gist.github.com/jmyzk/bb1a7328728c10385973ac8492c36181

https://youtu.be/hykNEg821KI

PM_Reeves

Hi @jmyzk_cloudsmart_jp

That looks like a lot of fun to try, but I'll need a coffee before I get going :D

Love this part of your answer "However, it's important to note that you can't place formulas in date columns used for dependencies.​😅"

That's an issue I was coming up against. I did wonder if it might be simpler to merely "count" the days On Hold and add that total to the duration when it comes out.

Thanks, and have a great day.

jmyzk_cloudsmart_jp

Have fun exploring!