Subtract Delays to Compute Percent Complete

ConcreteRusty

We are subcontractor using Smartsheet for scheduling of construction projects, we do use parent and children format. At times we are delayed by other trades which is added to the overall duration of the project and thusly will effect our percent complete. What I would like is to subtract the delay days and calculate a percent complete just for our portion of the project.

Jeff Reisman

@ConcreteRusty What is your criteria for calculating percent complete? Is it number of days worked so far out of the total duration of the project? Like say your project start is 1/1/23, project end is 5/31/23, today is 2/14/23. Ordinarily you might take total workdays between the project start and today, and divide that by total workdays between start and end dates. But you want to exclude days of no work because Steve's a really lousy electrician and he ran a bunch of 14/2 romex on a couple of 20 amp circuits, so now he's gotta start over, right? And the plumber thought it was cool to use shark bite fittings inside a wall (like a total amateur,) and now he's replacing a bunch with real soldered fittings, so the drywall still can't go up.

So how would you track those delay days? Just add a column where you keep a running total of the days delayed?

If so, then it would be something like =(NETWORKDAYS(StartDate@row, TODAY()) - TotalDelayDays@row) / NETWORKDAYS(StartDate@row, EndDate@row)

You mentioned Parent/Child rows, so depending on how that's structured, we probably need to make the above somewhat more complex to account for that.

ConcreteRusty

@Jeff Reisman Thanks for your help. I have created a column for Days Delayed, and a Company Duration Column. We will enter the number days delayed and then the top row will be a sum of those days. The top row of Company Duration Column will Subtract the delays from the Overall Durtation, (=Duration@row - Delays@row).

From here I want to use the %Complete column to determine the companies overall complete as is done in the Sheet for overall %Complete.

This what I have attempted so far. Any idea the sum of Ekedal Days Complete at the Parent level does not equal the sum of the Children?

Jeff Reisman

@ConcreteRusty Hard to say why things aren't totaling up without seeing the formula in the parent row.

Disclaimer: I don't work with Project sheets very much, I find them to be too restrictive for my purposes, so I'm not completely familiar with all the underlying pieces and relationships between the columns. I usually construct my own project mgmt sheets that fit my needs without anything extra.

One thing I've done when working with parent/child rows is to use the hierarchy functions to assign a Generation value to each row in a helper column. This let me specify which sets of rows to SUM or COUNT or whatever. So if you used =COUNT(ANCESTORS([Task Name]@row)) you would get 0 on the red Project Name row, 1 on the Slab and Walls rows, and 2 on the remaining rows.

Don't know if that helps you though, just because I don't know what's all built into the project sheet functions.

ConcreteRusty

@Jeff Reisman This actually helped. I checked the formula in the Parent Cell and it was calculatung based on %complete not a sum which makes a difference. Changing it to =Sum(Children()) got me the right numbers.

I have one more issue now. When I add a row to the bottom of a Family (is that a correct term) the formula for the children cells does not fillin.

Formula for the Children Cells of Ekedal Days Complete is =Duration@row * [% Complete]@row. When I add a row below row 18 their is no formula in that column.

Yes I am very novice at Smartsheet.

Jeff Reisman

Formula for the Children Cells of Ekedal Days Complete is =Duration@row * [% Complete]@row. When I add a row below row 18 their is no formula in that column.

Is the row already indented (aka already a child row of Slab?) Not sure if it will pick up the formula once it becomes a child row of the same parent.

Otherwise, you could try putting in that Generation helper column, and use an IF formula in Ekedal Days Complete:

=IF(Generation@row = 2, (Duration@row * [% Complete]@row), IF(OR(Generation@row = 1, Generation@row = 0), SUM(CHILDREN()), "")

After putting the formula in one cell, right click on the formula and choose "Convert to column formula" from the bottom of the context menu. That will apply the formula in that column to every row and to every new row.