Duration formula tied to allocation percentage

Beth Klineman

I am trying to create the correct formula tied to the percentage allocated for each task for the full duration

For example the total task has a duration of 54 Days. The Create task allocated .75 of the total duration (which is the same cell for each task) but I am not sure what formula should be put in the allocation duration cell. I tried different formulas(=Alloc.@row *[Duration]37; =Value([Alloc.]@row) * Value([Duration]@37]) and nothing worked. .75 of the total 54 days(this is in the Duration cell Row 37 should = 40.5. Thanks in advance for the formula help...I feel like it is simple but can't get it to work and need this formula for 150 different batches

Stefan

Hi @Beth Klineman,

this formula worked for me: =[Alloc.]@row * duration$2

There is no need for those brackets here "*[Duration]", but certainly in "Alloc.@row ".

In you second example "[Alloc.]@row" was good, but "[Duration]@37]" could not work.

Hope this helps.

Stefan

Beth Klineman

@Stefan that worked. Thank you -- now the Duration column is not populating with the number that appears in the alloc. duration. For this project I have included the dependencies section screenshot

Beth Klineman

@Stefan I have another follow-up - the formula works but the weighted durations are not equaling out -- look at the gap between row 68 and 69 tasks it is almost a week and half of no work? TIA for your assistance copying @Paul Newcome @Debbie Sawyer since I have seen them comment on other formula posts

Beth Klineman

@Stefan I am really hoping you can help -- or refer me to someone that can -- the forumla is now no longer working. Basically I need the following

• Task has a total duration (i.e 58 days) -- this is different for every batch image one
• We have added an allocation percentage breakdown for each task (i.e Create is .75 of the total days)
• I put the formula provided in the highlighted column and it gives me a 0 now -- if I use the following formula =[Alloc.]@row * Duration55 for each cell it gives me the correct breakdown but not a feasible option to have to edit 1000+ rows image 2
• If the alloc. duration is working properly why doesn't the duration column now show the same amount of days in the alloc. cell
• Last -- why would there be gaps in the last text if the total duration is calculated correctly but there is a gap of work

1 -

2 -

cc: @Paul Newcome @Debbie Sawyer

Debbie Sawyer

@Beth Klineman

Thank you for tagging me - I love formulae like this!

So I wondered if we could utilise the Audio Summary Duration value instead of the Batch 4 (FIB) value? If so we can use the following in Alloc. Duration Calculation:

=[Alloc.]@row * (PARENT(Duration@row))

Would this work for you?

Debbie Sawyer

@Beth Klineman

In this part of the thread

Your formula has not worked as you didn't fix the row 64 element in your formula.

I would anticipate in Allocation Duration row 66 your formula looks like this:

=[Alloc.]@row * Duration64

But it should be

=[Alloc.]@row * Duration$64

The $ in front of the row number fixes that row within the formula so that when you copy down all other rows within that group of children, they will all refer to the duration in row 64.

I believe in your screen shot, the row wasn't fixed and therefore the formula isn't using the total duration for the group at each child level.

Hence the broken results in Allocation Duration.

I hope this makes sense!

Kind regards

Debbie

Debbie Sawyer

@Beth Klineman

For this part of the thread:

The reason that the Duration column is not reporting the same as the Allocation Duration is that the Duration column is worked simply by the number of days between the Start Date and the End Date.

If you need the Duration to be calculated differently to 'End Date - Start Date' then you will not be able to have dependencies enabled.

There are ways of setting the dates in relation to other rows and then calculating duration using a formula but you cannot have dependencies enabled to achieve this level of control over duration.

I hope this helps.

Kind regards

Debbie

Beth Klineman

@Debbie Sawyer THANK YOU for saving me with the formula, and the duration Now I need help on the last question -- why is there a huge gap in date (row 329 and 330)from the second to last task and last task if my formula is breaking apart the total days per allocation percentage

Debbie Sawyer

What is the Predecessor data for row 330?

Beth Klineman

Sorry the screenshot did not have that see below each task should fall within the full duration

Beth Klineman

@Debbie Sawyer is there any way to continue to use the duration column? It seems the Alloc. Duration days are tied to the predecessors but I did not set it up that way.... we could feasible get rid of duration but would that impact anything else?

Beth Klineman

@Debbie Sawyer so I think I now see why we need the duration the total alloc duration needs to update the dates of each task

Debbie Sawyer

@Beth Klineman

It is your Predecessor data that is controlling the dates for your final task.

321FF means row 330 cannot finish until row 321 has finished, as therefore the finish date for row 330 is set by the finish date on row 321. If you changed the Predecessor data in row 330 to 329, then row 330 would have a start date of 09/06/23 and a finish date of 09/06/23 too.

Does this make sense?

Kind regards

Debbie

Debbie Sawyer

@Beth Klineman

Ahhh - I see, you have inherited this Smartsheet from another. That makes sense.

I'd be happy to jump on a zoom (right now) and explain the way the enabled dependencies are setting your dates and thus your durations.

It might be easier than messaging on this thread!

My email address is debbie.sawyer@smarterbusinessprocesses.com.

I'll monitor that email for the next 15 mins (as it is quite late in the day in UK!) and if you pop an email through I'll connect on Zoom MS Teams (let me know your preference) and we can talk it through! Shouldn't take too long.

Kind regards

Debbie

Debbie Sawyer

Sorry Beth, I'm going to need to log out now. Feel free to reach out though and I'd be happy to talk you through this.

Kind regards

Debbie