Allocation of Resources by Hour

Scenario

We have a smartsheet that tracks multiple projects (ROW) and we have resources allocated to one or many of these projects. In any given week, these resources are allocated on VARIABLE HOURS not %. Currently the smartsheet has % allocated for its Resoure Management View and it doesn't consider the HOURS (a total of 40) as it goes against the % allocated by DURATION.

Question

How can we have an EFFORT column in HOURS and use that towards Resource Management Views (at any given week), If they are over by 40 hrs in a week then they are overallocated.

Thanks

Find more posts tagged with

Project Management

Comments

Travis

Vijay - you could use this formula to calculate hours based off of allocation %. This wouldn’t show in your resource views, but it would be a good visual in your sheets:

=[Allocation %]1 / 1 * 8

This assumes 100% of your day is 8 hours. You can adjust this if your days are longer or shorter than 8 hours.

James Harr

Is there a way to generate an Allocation % column from a DURATION and EFFORT column? In a lot of ways this is preferable for us. Sometimes we have a task we know takes about 4 hours worth of work, but might be spread over 1 week. I could type in 10%, but if then if the task gets updated to be complete in 2 days, I have to manually futz with the % allocation column so it accurately reflectes the amount of actual work.

Terri Pavlovic

I've done something similar. I added a new column called "# of Hours for Task". In this column the effort of the task will be added.

In the "% Allocation" column I have a formula:

IF(Duration1 <> 0, [# of Hours for Task]1 / (Duration1 * 8), 0)

It checks to see if this is a milestone task (with a duration of 0) and if not will calculate the allocation percentage based on an 8 hour day. If there are no hours specified for the task, it defaults to 0%. You could alternatively default this to an allocation % of 100.

As an example, if you have a task with a duration of 5 days, and effort of 8 hours, it will generate an allocation % of 20%.

Jamie Camp

Are you using [% allocation] for resource loading? I like this idea but cannot enter a formula in the [% Allocation] column will that column is assigned as the allocation coulmn under the resource managment settings.

Terri Pavlovic

Hi Jamie - the formula I put into the % Allocation field was done before I turned on resource management. Once you turn on resource management, you can no longer edit the formula. I've found that it stays there and still works, but it doesn't copy when you add new rows. I've since found out that using formulas in the % Allocation is not supported by Smartsheet, so I suppose it could stop working at some point.

I do wish there was a better way to calculate % Allocation rather than manually figuring out the % based on how long you know the task takes. For us, we often give people X days to finish an Y hour task. We know they have other things to do and other projects to work on. We don't want them allocated at 100% for all X days, so you have to manually figure out the allocation (e.g. a task that takes 8 hours to do, but they have 5 days to do it, would be 20% allocation).

Ezra

Can't believe this is so hard to accomplish. It's easy enough to write a formula that gives me a % of [work hours]@row / ABS(duration)@row ... and a quick if to check for div by zeros.... but then setting that column to be the allocation just hard-codes the numbers and all formula logic is lost.