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

01/06/21 Edited 01/06/21
Accepted

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!

Best Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    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.

    thinkspi.com

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    edited 01/12/21 Accepted Answer

    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)

    thinkspi.com

Answers

  • Mark CronkMark 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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • edited 01/11/21

    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 CronkMark 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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • 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 CronkMark 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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    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.

    thinkspi.com

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    edited 01/12/21 Accepted Answer

    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)

    thinkspi.com

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    edited 01/12/21

    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)

    thinkspi.com

  • 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 NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

Sign In or Register to comment.