Converting duration into effort?

Tony Platts

Our project managers create plans as part of our pre-sales process. We add in the tasks, duration and the %age allocation.

We then need to calculate the amount of hours so that they can be included, as an estimate, in the Statement of Work provided to the customer.

How can I get a list of expected effort on a project?

So far I have added a column that says =IF([% Allocation]@row > 0, Duration@row * 8 * [% Allocation]@row)

In theory, this works, and I was surprised it even works when I change duration to weeks or hours instead of days. However, what it does not account for is the number of people involved in the task. If a task takes one person one day for 100% allocation the figure needs to be 8 hours. If the task takes 3 people, it needs to increase to 24 hours.

This is simple in any other software. Can you tell me how I get this value please?

Paul Newcome

Assuming your allocation column is a multi-select contact type column, you can leverage the COUNTM function to further multiply the output by the number of people allocated on the row.

=IF([% Allocation]@row > 0, Duration@row * 8 * [% Allocation]@row * COUNTM([ASsigned To]@row))

Paul Newcome

Assuming your allocation column is a multi-select contact type column, you can leverage the COUNTM function to further multiply the output by the number of people allocated on the row.

=IF([% Allocation]@row > 0, Duration@row * 8 * [% Allocation]@row * COUNTM([ASsigned To]@row))

Tony Platts

Thanks Paul, that's exactly what I'm looking for.

Considering the general subject of effort and hours per task, is there any best practice advice you would offer for a new Smartsheet user coming from MS Project?

Paul Newcome

@Tony Platts I unfortunately don't have any experience with MS Project, so I wouldn't even be able to begin to outline what differences there may or may not be.

MarceHolzhauzen

Hey Paul

Do you perhaps know if this would work for resource management too?

Paul Newcome

@MarceHolzhauzen Are you referring to the premium add-on Resource Management, or just the idea of resource management in general?

MarceHolzhauzen

@Paul Newcome for the Premium add on. For the add on, you can only assign one person to a task, but i was wondering if you manipulate the formula in a normal sheet, if it would apply it to RM?

Paul Newcome

@MarceHolzhauzen It may work, but I have never actually tested it before.

Tony Platts

Resource Management only seems to carry over set fields,

• Start Date
• End Date
• Assignee
• Allocation %age.

So, the column which Paul helped me create isn't relevant in any way to Resource Management.

What's worse is that this completely rules out the possibility of using duration in hours in Smartsheet and then expecting them to be carried over to Resource Management.

As an example, an hour long task in Smartsheet, entered as 1 hour at 100% allocation, appears in Resource Management as 1 day of work. It passes over the start date, the end date, and calculates the duration as 1 day. It then applies the 100% to the capacity plan.

The only way to fix this is to create a task which is 1 day long and setting the duration as 12.5%. This carries over correctly into Resource Management but then you have the problem that creating 8 tasks, that are supposed to be 1 hour in duration, spreads out over 8 days if you use predecessors.

The Resource Management onboarding video does say that the duration is passed over but this isn't and, in fairness, they do caveat it by saying that it's talking about future functionality. Honestly, the integration between Resource Management and Smartsheet just isn't for purpose at the moment, unless you're working on large project at high level.

Tony Platts

I can tell you that it does not work.

The problem is that, despite what the Resource Management onboarding video tells you, the only fields passed over from Smartsheet to Resource Management are;

• Start Date
• End Date
• Assignee
• Allocation - taken as a percentage.

If you set the Allocation Column to the "Work (Hrs) and you've got 9 hours, Resource Management sees this as 900% allocation.

This is how I configured it.

This is what shows in Resource Management.

If you set the duration to 1 hour, and the percentage to an actual % allocation, Resource Management interprets this as a task starting on the 5th June, ending on the 5th June and having 100% allocation. In Resource Management this books a full day.

Honestly, this really isn't fit for purpose and, until it's resolved, I'm genuinely considering requesting a refund for our licence costs.