Effort Vs Duration

edited 12/09/19 in Smartsheet Basics


I am new to smartsheet and wondered if you could help.

I would like to create a project plan which shows the effort required vs the duration.

for example: a task may take 2 days to complete however they can complete this anytime within a 5 week time spell - how would I show this on the plan so that the resource allocation remains accurate?




  • Hi Em,

    We use a combination of duration and resource allocation % to show this. If the duration of the task is 5 weeks (5 day business week) and it takes 2 days to complete the task, you'd set the duration at 5 weeks with them assigned 12.5% on the task in the allocation % column.

    Otherwise, you can set your sheet up with text/number columns and formulas to calculate this, although this can't be used with the Resource Management functionality that Smartsheet has to offer.

  • Hey, Shaine -

    Why do the formulas break when you assign the column to be the Allocation % column under Project Settings > Resource Management?




  • Hi Dan—

    Formulas can't exist in columns that are also being used for resource management (including Allocation %). If you place a formula in a column, then use that column for resource management at a later time, the formula will be removed.

  • So, to leverage this solution we can add a work effort column, enter the allocation %, and any time the work effort value changes we need to manually recalc the % allocated and update, correct?

  • The way I tend to get this working is to have the following columns:

    • Duration - functions as normal
    • Allocation % - holds a %, set as the Resource Allocation % column for the project
    • Effort - Formula: Duration * Allocation %

    It can be a bit of a pain when you're trying to plan based on Effort and Resource Allocation (which for me is the most common way), but it achieves the same results.

    So in your case, when the work effort value needs to change, you would simply adjust either or both of Duration and Allocation %. Usually you would know whether a resource can be more or less available, and/or whether the task needs to finish on time (and which of these is more important), so this should work ok.


  • Pete Cutter
    Pete Cutter ✭✭
    edited 11/07/18

    I hope SmartSheet prioritizes building in the flexibility to accommodate the issues raised in this thread. If the ability to edit duration and effort were detached (with a range of options for how the sheet manager wants them to interact for: resource management, F-->S dependencies, duration auto-calculation, etc.), this would be a much more valuable and usable tool for our team. 

  • Stu Benoff
    Stu Benoff ✭✭✭✭

    I'm also looking for an improvement in Smartsheet to address %effort vs. calendar duration.   I would also want this to consider %complete for active/started tasks so that on/off schedule could be derived.  


    Anyone know if this is on the roadmap?



  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi everyone,

    Please submit an Enhancement Request when you have a moment to have your vote added

    A New Way to Submit Your Feature Requests

    To make your Enhancement / Feature Request count, send in the form above because there isn’t a guarantee, it will be registered otherwise.

    Original Post: https://community.smartsheet.com/announcement/new-way-submit-your-feature-requests

    Have a fantastic weekend!


    Andrée Starå

    Workflow Consultant @ Get Done Consulting


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Ezra
    Ezra ✭✭✭
    edited 06/14/19

    has anyone filled out the Enhancement/Feature Request for this?

    I think it would be great to have a checkbox next to the column-picker (default checked) for selecting percentage or minute/hour/day values.



  • Stu Benoff
    Stu Benoff ✭✭✭✭

    I submitted an enhancement request for this feature.   I'm not sure that placing a checkbox where you suggest would completely address this since resource availability can be different by resource and can change over the course of a project.   I was looking for something that would apply at the task level, that perhaps could default from the project level, but I need further flexibility over the course of the project.  

  • Hello Everyone, any updates on this thread? I am struggling for correct % completion in parent task. Use case a small effort task is spread out on a large timeline. If a task is given 3 weeks to complete but it is a small effort of 4 hours, Parent % completion gives it a big weightage because it can be completed at any time in a given 3 weeks of completion window.

  • Hi @Sudhir Singh I am also going through the same issue as you.

    After many many iterations, I decided to make it simple creating 2 columns for Total Effort and Effort X % Complete

    The only Durations taken are if Children so my formulas are:

    Total Effort Rows: =IF(COUNT(CHILDREN()) > 0, 0, Duration@row)

    Effort X % Complete: =IF(COUNT(CHILDREN()) > 0, 0, [% Complete]@row * Duration@row)

    Final Cell for Real % Complete: =SUM([Effort X % Complete]8:[Effort X % Complete]37) / SUM([Total Effort]8:[Total Effort]37)

    I wish there was a simpler way but was not able to find one....