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

Guang ✭✭
edited 08/25/23 in Resource Management

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.

Best Answer


  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 08/26/23

    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.)

    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.

    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
    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?


  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓


    Yes, if you enable dependencies.

    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?