Elapsed Duration Formula with Same Weighting of % Complete

Megan MacKenzie Benefiel

I am trying to create a column that calculates the "elapsed duration" of a task and rolls up to the parent rows in a comparable way as the % complete column. I would use this to generate automatic status indicators, so if the % complete is less than the elapsed duration, the project status is in yellow or red.

The issue I'm having is the % complete is not just date based, it seems to be weighted against the task durations. This is creating a mismatch between the % complete and elapsed duration. Since I can't change the % complete column, has anyone come up with a formula for this that matches the weighting in the % complete roll ups?

My current formula is this (but again, this is strictly based on dates so it does not align with % complete):

=IF(TODAY() <= [Start Date]5, "", IF(ISBLANK([End Date]5), "", IF([End Date]5 <= TODAY(), 1, NETWORKDAYS([Start Date]5, TODAY()) / Duration5)))

This creates an issue in the parent rows when there are short tasks across a long period of time where we may be completing the tasks on schedule, but the elapsed duration thinks we're significantly behind. You can see an example of this below:

SoS | Dan Palenchar

Do you want the parent row of "Elapsed Duration" to be the average of the children rows (similar to % complete)? If so, use the below formula in the cell of the parent row only.

=AVG(CHILDREN())

This is what I produced.

The formula I used is a bit different, but functions similarly with the exception I am displaying a dash "-" rather than a blank cell for projects starting TODAY or in the future. I find it to be a best practice to have formula columns always return some value so you can easily see if they get lost/ are not auto filling.

Also, I would recommend justifying your text the same across your sheet so it is easier to read. Specifically the Duration column is top aligned while the rest of the Sheet appears to center justified so it

Megan MacKenzie Benefiel

Thanks for the response! The parent rows of % complete don't seem to be straight averages. They seem to put some weight on the duration. The issue I have is if I update % complete on a task to match the elapsed duration, I want the parent rows to match. Currently, because I don't know exactly what calculation is being used for % complete, they're often off even when the numbers match exactly. This can result in reporting an incorrect status.

Thanks for the feedback about alignment.