Formula in Resource % Allocation column

I have added a column to put in the number of hours I would expect the task to take each day.

I prefer this to having to work in percentages of a day and think this will be easier to request of my team.

To allow this I need to put a formula in Resource % Allocation column to divide the number of hours by the number of hours in a day. [=hours/7.5]

But as soon as I go into project settings and set the calculated column as the Resource % Allocation column it removes the formula and replaces it with the result. Changing the number of hours then has no effect on that value.

So... How do I make a calculated value the Resource % Allocation column?

Find more posts tagged with

Sheets

Formulas

Comments

Richard Rymill SBP

Hi Rob

There are some notes in the Help files but here is a good example....

Resource Management Formulas

This will calculate the number of hours worked (based on an 8 hour day) and percent allocated[Allocation %]1 / 1 * 8 + " Hours"

For Resource Management in Hours: Allocation % is the name of your allocation column and its in row 2 (in this example). This formula assumes that a whole day is 100% of your time and is 8 hours long. If you have longer or shorter work days, you can change change the "8" to however many hours a 'full' workday is for you."Add a column called Hours in your sheet and use this formula:

=[Allocation %]2 / 1 * 8

With some text at the end:

=[Allocation %]2 / 1 * 8 + " Hours""

Hope that helps

RichardR

RobT

Thanks for your reply.

Could you clairfy what you are suggesting as it seems to be a circular reference?

I tried copying your formula into the column but as I said the formula disappears in the Allocation % column - it won't accept formulas - including the one you suggest.

I have two pieces of information:

1 The number of hours per day that are required for the task

2 the number of hours in a work day

My proposal is to create a formula taking these two pieces of information to calcualte the % of a day.

This formual would sit in the % Allocation column.

Your suggestion seems to require the % Allocation figure and take this to calculate the number of hours. But it is the % Allocation I'm trying to calculate.

Have I mis-understood your answer?

So... in summary - why can't I put a formula in the % Allocation column might be the question?

RobT

It turns out the answer's simple: it isn't going to work as formulas in project setting columns are not supported.

https://community.smartsheet.com/discussion/allocation

Shaine Greenwood

Hi Rob—You're correct. At this time, there isn't a way to place custom formulas in a column being used for resource management or dependencies, as formulas are already performing calculations on the backend.

I've passed your vote along for a way to use formulas in these columns on to our Product team for further review.

Anne Cousins

I would like to add my vote for adding a formula to a project setting column as well.

Christopher Young

I would like to add my vote to this too

Brian Ingle

This is exactly what I am trying to do so I would also like to add a vote. Is there somewhere we can add requests and vote on them as users ?

Sarah Geary

I have reviewed several chains with users expressing this as a frustration. Why is a formula not allowed in the Allocation column?? Who actually has the time to manually calculate the percentage? Our team can estimate number of hours, and it should be simple to have the sheet determine the % allocation based off of the Duration. This renders the feature unusable.

rherrera18181

I read this thread, and I agree. Being able to to specify number of hours is more in line how we work. Once specifying the number of hour to allocate, percentage should be calculated.

Can this feature be added?

pbylow

I have been searching for ways to create this same functionality hack to show allocated hours that would would trickle down to my resource views - but I've not been successful.

Has there been any movement on the Smartsheet side to add functionality for allocated hours instead of only percentages?

Thanks!

Bobbie Pugh

I'd like to add my vote and also request assistance on a formula that I've created to try and calculate the % so I can manually update the Allocation % column. It works great for full day durations, but isn't calculating properly in the event I have tasks that are partial days... it's calculating 100% allocation. I added a column called Calculated Allocation % and am using the following formula based on 8 hours in a workday:

=IF(Duration52 <> 0, [Estimated Hours]52 / (Duration52 * 8), 0)

While not ideal, it has proven helpful and it would be even more helpful if the formula worked for hours duration but I'm not savvy enough with formulas to figure it out

This would also help with the scenario where I have multiple rows for tasks that are assigned on a single day and could be set up as xxh or .xxd:

7/20 to 7/20 2h Task 1 Allocation % should be 25%

7/20 to 7/20 .5d Task 2 Allocation % should be 50%

Any assistance that could be provided will be GREATLY appreciated as our project plans generally have an average of 250 rows/tasks and trying to calculate the allocation percentages is proving extremely taxing.

Thanks in advance,

Bobbie

Laura

add mine too

JimmyStewart

Yes, we also need this ability. Not sure if Smart Sheet offers a feature request platform where we can vote up these as I'm not sure these forums are monitored closely.

OysteinOren

Please add my vote for this, this have to be a design error. The requirement to use percentage allocation kills the potential of the resource view feature.

Nobody plans in percentages of workload. Percentage of workload should be a result of your estimated hours/days/weeks and duration, not a data input.

If I

1 Define a working day as 8 hours

2 Have got a task of 8 hours

3 Estimate allocation as 8 hours, 1 day, or 0,2 weeks

4 Plan start the 1st day, and finish the 2nd day

5 This should result in a 50% allocation between 1st and 2nd day in the resource view.

6 If I change the task lasting from 1st day to 3rd day, this should result in 33% on the resource view.

The way it is designed now, I have to make an additional step of calculating and changing the %Allocation for it to become correct. With a large amount of tasks and more complex numbers, this becomes to rigid to be used.

OysteinOren

Sorry, I seem to have misunderstood. There is no need for a feature that allows formulas in the allocation column, I believe that would risk circular references.

There IS however a need to change the %allocation to hours/days/weeks allocation.