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

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.

Find more posts tagged with

Accepted answers

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.