#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# How to calculate effort relative to working hours?

Options
edited 12/09/19

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

• Options

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

• Options

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

• ✭✭✭✭✭✭
edited 09/01/16
Options

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:

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

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

• Options

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?

• Employee
edited 09/26/16
Options

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.

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

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.