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



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?



  • jmyzk_cloudsmart_jp
    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."😀

  • PM_Reeves
    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
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Have fun exploring!