How to estimate hours per Task using % allocation, Assigned to and Duration Columns?

Pedro Concílio

Hi Everyone,

I hope you all are doing alright.

We need to estimate how many hours will problaby be spent on each task. We already use %allocation, Assigned to and Duration columns in our cronograms. We can't use the suggested formula "%allocation * Duration * 8" because:

1) Multiple employees can be assigned on the same task. I have no idea how Smartsheet can "break" the cell to read which employees are assigned to the task.

2) Not all of our employees are full-time, we also have part-time and interns assigned in our projects, so the number of hours worked per day may vary according to the Employee. We already have a sheet that contains the employees names and hours worked per day.

Do you know how can I calculate it using Smartsheet?

Thank you and have a great week!

Paul Newcome

To do this with multiple contacts, you would need to parse out each contact into its own column across the row. Then you would use an INDEX/MATCH for each of the individual columns to pull the hours.

Paul Newcome

HERE is a link to a parsing solution that pulls data from a list across a row into multiple columns based on a specific delimiter.

To modify the linked solution, the JOIN column would be your multi-contact column, and you would replace all instances of

"/"

with

CHAR(10)

Mark Cronk

Hi @Pedro Concílio ,

Can you share some screetshots of your sheet columns to help us understand what you're looking for?

Thank you,

Mark

Pedro Concílio

Hi @Mark Cronk ,

Thank you for the response.

Sure! As seen below, we have "Duration", "% Allocation" and "Employee" columns.

On another sheet, we have the amount of working hours per day per employee.

So basically we want to create an Column called "Hours" and estimate how many hours will problably be spent on each task. For example, if we want to estimate how many hours will be spent on the task below, the calculation would be:

Duration: 5 days / Allocation: 10% / Employee: Gerente and Pedro Concílio / Hours worked per day: Gerente - 8,5 hours; Pedro Concílio - 6 hours

Hours = 5*0,1*8,5 + 5*0,1*6 = 7,25 hours.

This column would be really important to us. I hope that you can help!

Thank you very much and have a great week!

Mark Cronk

Hi Pedro,

This is a real challenge. You're asking to parse out values from a multi-select contact list and then look up values for each in order to do other math. I need to give it some thought. There are lots of smart people in the Community. Someone else may offer a solution before me.

Mark

Pedro Concílio

Thank you for the response, Mark!

Do you know how we could do it if we ensure that the Employee column will be filled with one contact only? We are considering to change the column settings to make this calculation easier.

I was thinking about using a formula similar to Excel "=index(...,(match..))" to extract the "Hours worked per day" from the other sheet, so it would "read" the Employee name on the main sheet and get the Hours Amount from the other one. Unfortunately I don't know how to do it in Smartsheet.

Have a great day!

Mark Cronk

Hi Pedro,

With a single contact it would work just like Excel. You'd do an Index-Match or Vlookup to find the hours worked by the employee and pull that into your formula. There has to be a way to do it with multiple contacts though. Someone will have an answer.

Mark

Paul Newcome

To do this with multiple contacts, you would need to parse out each contact into its own column across the row. Then you would use an INDEX/MATCH for each of the individual columns to pull the hours.

Paul Newcome

HERE is a link to a parsing solution that pulls data from a list across a row into multiple columns based on a specific delimiter.

To modify the linked solution, the JOIN column would be your multi-contact column, and you would replace all instances of

"/"

with

CHAR(10)

Paul Newcome

NOTE: The above modification is inaccurate. CHAR(10) is not the delimiter in a multi-select contact column. I will need to do some digging to try to find out what the delimiter actually is.

EDIT: Even though it is not visible, the delimiter is ", " (comma space)

Pedro Concílio

Hi @Paul Newcome

It worked!!

Thank you very much for your help, it will be very useful for our company.

Have a great day!

Paul Newcome

Happy to help. 👍️