How to allocate estimated work hours evenly across the duration of days

sbens

Hello,

I am using Smartsheet for my Project Plan, and we are wanted to assign estimated hours to each task on the plan so that we can see if Resources are over-allocated in any given date range. I only have access to the legacy resource system, so I am hoping there is a way to do this with a formula.

I have columns for the Start, Finish, % Allocated, Assigned To and # of Hours per Task. I have been able to create a grouped report that sums the Hours assigned to an individual by week. But I am getting stuck when trying to take it one step further and see the estimated hours by day because some tasks have a duration of a few days.

For example, if the date range is 4 days, say start day isMonday and end day is Thursday, and we estimated the task will take 8 hours, I would like to see that the resource is assigned 2 hrs a day for each day in the range. Then be able to sum up the allocated hours from all of the tasks in the plan and see the total hours of work, per day, that each resource has assigned to them.

Is this possible without using Resource Management? Thank you in advance for any guidance!

Zachary Hall

Hi @sbens,

I would recommend making this easier by adding a helper column to calculate the [Daily Hours] to prevent the final formula from being too long. Something like:

• ={Hours per task}/({Finish}-{Start})

Then it would be a matter of using a SUMIFS formula to add it for applicable dates. The example below is written for today:

• =SUMIFS([Daily Hours]:[Daily Hours],[Assigned To]:[Assigned To],"Example",[Start]:[Start], <TODAY(), [Finish]:[Finish]>TODAY())

I hope this helps!

sbens

Hi Zach,

Thanks so much for your response. I have added the Helper Column to calculated the allocated hours per day, it is just the Hours per Task/Duration.

I'm not understanding the SUMIFS formula. I applied it to a new column on my sheet and I receive a #divide by zero error message, but there are no 0s.

I am attempting to have task hours / duration and then those hours to be assigned to the individual days within that duration so that I can make a report of all the hours assigned by day to a resource.

Does the second formula you provided need to be in the Project Plan, or applied to the report where I am doing the actually summing of task,hours,resources?

Thank you

Zachary Hall

It is odd that you got a #divide by zero error message when there is no division within the SUMIFS formula. The first formula would only be returning a zero if the start and finish date were the same. If that's the case you could modify the formula to just report the hours if it is a single day project:

• =IFERROR({Hours per task}/({Finish}-{Start}),{Hours per task})

The SUMIFS() is the formula to add up the hours on any given date. The example I provided was written to calculate them for TODAY(), but could be applied to any date range.

As far as where you have the formula it would really just depend on where you want the information about the individual to be displayed. From your description I think it would be in the plan. If that is a separate sheet, then your [Daily Hours]:[Daily Hours] would need to be set up and replaced with the inter-sheet range {Daily Hours}. I hope that makes sense! Let me know if I'm missing something, and can help!