How to calculate effort relative to working hours?

As a new user I'm working my way through the Smartsheet product. Thanks to the 'best answer' in this post I was able to calculate the Effort (or Energy), which is visualized by 1 to 5 people-icons; this way Duration can be set or calculated for regular project planning, but in Card View this is automatically shown by an indicator as Effort per task:

1 puppet = more then 0 minutes, but less then 4 hours

2 puppets = 4 hours or more, but less then 1 day

3 puppets = 1 day or more, but less then 3 days

4 puppets = 3 days or more, but less then 5 days

5 puppets = 5 days or more

This is visualized per row within a column called Effort, with a type of Symbols. Now I've choosen for the People (1 - 5 puppets) symbol, but maybe I'll switch to the Levels of Pain (1 - 6) symbol. As I want to work with estimated time per task / user story (input), this Effort is automatically calculated by this formula:

=IF(CALCDURATION(Start2, Finish2) >= ABS(5), "Five", IF(CALCDURATION(Start2, Finish2) >= ABS(3), "Four", IF(CALCDURATION(Start2, Finish2) >= ABS(1), "Three", IF(CALCDURATION(Start2, Finish2) >= ABS(0.5), "Two", IF(CALCDURATION(Start2, Finish2) > ABS(0), "One", "Empty")))))

So far so good...

This hard-coded approach works fine with the default schedule of 8 hours per day and 5 days per week. But, I would like it to be more flexible, so the visualization becomes relative to the available hours (and people) per week for the project. So for this I have two questions:

1) How can I change the formula so it uses a percentage of the default Working Hours available per week?

2) With a possible change to a 'Levels of Pain' indicator; how could the workload be shared by two or more people and let this reduce the percentage for the task?

Maybe you would like to use the approach above as-is, but I hope you also would like to make some suggestions to get this one more flexible. Thanks!

Best regards,

Maik

Find more posts tagged with

Healthcare

Education

Formulas

Services

Project Management

Card View

Media & Entertainment

Comments

Maik van Rossum

Is there another reference for functions like CALCDURATION? I really would like to implement the functionality above.

Maik van Rossum

Someone from the Smartsheet-team, anything on the reference question? Thanks!

Gwyneth C

Hi Maik,

I manage the Help Center at Smartsheet. I’m happy to address your question about whether there are additional documentation resources for functions around duration and resource allocation. As you’ve likely discovered, Smartsheet functions available for public use are documented here:

https://help.smartsheet.com/articles/775363-using-formulas#Formula_Reference

To see examples of functions used in formulas, I also recommend this Smartsheet template:

https://www.smartsheet.com/solutions/smartsheet-formula-examples

The CALCDURATION() function is undocumented because it’s designed to be used internally by Smartsheet to calculate duration on sheets when dependencies are enabled in a sheet. This type of function isn’t intended for public use because of its very specific use case: the functions that we document and make available for customers are more flexible and can accommodate the wide range of use cases that customers need. So the answer to your question about whether other resources exist is...no there aren’t additional resources beyond those mentioned above (and what you’ve found here in the community).

It sounds like you’re making good use of Card View to visualize a project that you’re planning. This type of visualization of resource allocation for project planning isn’t a scenario that we’ve seen much yet, so reaching out to the community–as you’re doing–is a great idea.

Cheers!

Gwyneth Casazza

Customer Experience Manager, Smartsheet

Maik van Rossum

Thanks for your response I can see that there are some undocumented functions, and I think that I can understand why. However, as mentioned in this other posting (see link in first line) in some cases your team is willing to help and share by request. So is there an undocumented function that I can use to calculate the percentage of the available working hours?

Shaine Greenwood

Hi Maik—Happy to respond here. You're correct in that we do have functions that aren't documented in our Help Center, and as Gwyneth pointed out: undocumented functions are used behind the scenes for functionality that we've built into Smartsheet. (Although we don't prevent manually typing in an undocumented function for use in custom formulas.)

We currently don't have a function (documented or undocumented) or workaround that can be used to calculate available working hours as a percentage, as we don't yet have Effort functionality in the application.

I'll pass your feedback to our Product team for Effort calculation to be added to a future version of the app.

I also wanted to provide more information based on what I've read in this thread on your process:

Currently, it’s not possible to assign a task to more than one person and have that impact duration.

More information on that is available here:

http://help.smartsheet.com/articles/518335-assigning-people-to-a-task

If you enable dependencies on the sheet, Smartsheet will calculate the duration for you–which means you won’t have to use the CALCDURATION() function. With dependencies enabled, you’ll also be able to customize working hours and days beyond the standard 8 hour day…although, to reiterate, we don't have a way of expressing all available hours in a percentage.

More information on how this works in Smartsheet is available here:

https://help.smartsheet.com/articles/516392-defining-working-non-working-holidays-on-a-project-sheet