How to make the value of “Duration” field be updated automatically when I change the assignees.

Guang

Hello SmartSheet Team,

How to make the value of “Duration” field be updated automatically when I change the assignees. For example, there is one task effort is 8 person days, if I assign two members as the processors for this task, I suppose the “Duration” should be 4 person days automatically. But the value of “Duration” is still 8 person days, there is no any change. May I know how to make “Duration” could be updated according to the count of the members.

Thanks in advance.

jmyzk_cloudsmart_jp

@Guang

Yes, if you enable dependencies.

https://help.smartsheet.com/articles/765737-project-sheet-columns-start-date-end-date-duration-complete-and-predecessors

You do have an option of disenabling the dependencies function; then, you can have formulas to calculate duration, etc. But that removes most of the Smartsheet's charm, like automatic start and finish date adjustment.

Changing the duration based on the assignees is a tricky business. Some assignees spend 20 or 50% of their time, while others spend 100%.

 I know very little about OmniPlan, but it looks similar to the Smartsheet of MS Project. If you can access a Smartsheet account, why don't you play with various functions and formulas to find one that satisfies your needs?

jmyzk_cloudsmart_jp

Hi @Guang

While making updates automatically tricky, you can use helper columns to facilitate updates.

Please take a look at the published demo Project Management Sheet.

(You can edit Duration and Assigned to Columns to check how the helper columns work.)

https://app.smartsheet.com/b/publish?EQBCT=e9c56d4a0cb6406d85d06245982875a8

It is difficult to fully update duration automatically because you typically want to enable dependencies in Project Management, but functions such as Duration, Start, Finish, etc., cannot be used.

https://help.smartsheet.com/articles/765737-project-sheet-columns-start-date-end-date-duration-complete-and-predecessors

Enable dependencies

If you enable dependencies in the Start Date, End Date, Duration, % Complete, and Predecessors columns, you cannot use formulas.

Helper columns to facilitate Update

How To Use the Helper Column

If a flag is in the Update Column, copy & paste the [Task Effort / Count M] values to the Duration column.

• Count M: Count Number of Assignee
  • =COUNTM([Assigned To]@row)
• Task Effort / Count M: Calculate Task Effort in Person Days per Assignee
  • =INT([Task Effort]@row / [Count M]@row * 1000 + 0.5) / 1000
• Update: Flag if there Duration <> Task Effort / Count M
  • =IF(Duration@row <> [Task Effort / CoumtM]@row, 1, 0)

Please note using INT in the above formula to adjust the number of digits.

Guang

Hi @jmyzk_cloudsmart_jp ,

Thanks a lot for your reply. It is very useful for update the "Duration" automatically.

Do you mean it is cannot update the "Duration", "Finish Date" automatically, if I change the processor count for a task?

I want to implement the OmniPlan's project management function on web based. May I know if SmartSheet can implement it?

Or do you know if there is any web production that have the same functionality as OmniPlan?

Thanks.

jmyzk_cloudsmart_jp

@Guang

Yes, if you enable dependencies.

https://help.smartsheet.com/articles/765737-project-sheet-columns-start-date-end-date-duration-complete-and-predecessors

You do have an option of disenabling the dependencies function; then, you can have formulas to calculate duration, etc. But that removes most of the Smartsheet's charm, like automatic start and finish date adjustment.

Changing the duration based on the assignees is a tricky business. Some assignees spend 20 or 50% of their time, while others spend 100%.

 I know very little about OmniPlan, but it looks similar to the Smartsheet of MS Project. If you can access a Smartsheet account, why don't you play with various functions and formulas to find one that satisfies your needs?

Guang

Thank you @jmyzk_cloudsmart_jp .