Duration as a formula to accommodate resource allocation varience

JamieRH

I have three columns in my schedule sheet. Duration (native column) hours and resource allocation. I would like the Duration to be my hours divided by my resource allocation. So If I plan to put 2 people on a 80 hours project my duration is only 5 days not 10. I tried a formula in my duration column but it doesn't allow it.

Can that be done?

Anything else I can do? 

J. Craig Williams

Not much automatically.

Duration can not be calculated. [% Allocation] does not impact it, Resource Management takes Duration and [% Allocation] as inputs, but there is no feedback.

Smartsheet is very weak in this area.

In Smartsheet, I find the best alternative is often to turn off Dependencies and do the calculations myself (in the tool). Sometimes I keep two copies of the schedule, one with Dependencies and the other without, but I don't think that will help in your use case.

Craig

Alter Ego

I have exactly the same business need as JamieRH above, and got stuck in the same place - then I saw this comment above by J. Craig Williams, and spotted that this request and response came in a number of years ago. It is now 5 years down the line since the response above... And I was wondering whether anything had changed (as the function requested is a fairly basic function that is available in most traditional stand-alone project management software products).

To me it looks as if Smartsheets still cannot do this - is this conclusion correct?

Genevieve P.

Hi @Alter Ego

You are correct, the Duration column has it's own specific formula applied if you're using dependencies. If you need to add more criteria to how it's calculating, such as using an allocation column, you would need to create your own calculations and use a formula.

Cheers,

Genevieve

Alter Ego

Thx for confirming @Genevieve P.

Any examples of calculations on how someone have achieved this and got the duration bars in the Gantt chart to grow or shrink would be welcome. What I would not want to do is to spend weeks attempting to get this done, when it actually is not practically possible in the current version of the tool.

Genevieve P.

Hi @Alter Ego

You would need to set up your own Duration formula based on what your specific needs are, but once you have a numeric output as the "number of days", you can then use the WORKDAY function to add that number of working days to your Start date to create the Finish date.

There are a couple things to keep in mind:

• The Date columns used in a Gantt chart cannot be set as a Column Formula so you'll want to drag-fill it down
• Using formulas means that you will not be able to use dependencies, so the tasks won't have predecessors or connections in the view.

An alternative would be to use the default dependency settings but ensure your tasks are broken out as child rows per-resource so there's only 1 resource on each row. Then use the Parent Rollup Functionality to have the top-level row display an overall duration and percent completion. (Here's an old post with an example).

As a side note, if your primary concern is resources, I would recommend looking into Resource Management.

Cheers,

Genevieve