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

Pedro Concílio
edited 01/06/21 in Formulas and Functions

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

Answers

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


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

  • Pedro Concílio
    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 Cronk
    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


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


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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 01/12/21 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)

  • Paul Newcome
    Paul 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)

  • Hi @Paul Newcome

    It worked!!

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

    Have a great day!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!