is there a way to make children auto-calculate percent complete based on start/end date and the curr

I'm new to this program but not scheduling in general. Is there a way to get each child beneath a parent to calculate % complete automatically, based on the entered start/finish dates and the current date? I'm building construction schedules, and don't want to have to manually update huge amounts of child rows, especially when everything is going as planned.

Find more posts tagged with

Construction

Formulas

Reports

Community Platform

Conditional Formatting

Gantt View

Comments

Pam Alakai

Hello,

We did not yet implement the ability to auto-calculate the percentage complete in children rows based on the start and date dates. But please add your vote for the ability to do this here so that it can be considered by our product team for future implementation!

Kind Regards,

Pam

Smartsheet Support

CanadaJim

There are formulas by which you can do this manually. You can use the NETWORKDAY function to calculate how many work days are between your start and today, and divide it by another NETWORKDAY using the start and finish dates. Use it in your child rows.

=NETWORKDAY([StartDate]1,TODAY())/NETWORKDAY([StartDate]1,[EndDate]1)

You'll want to put some IF statements in to make sure today is actually between the start and end dates. If not, it'll either be 0% or 100%.

Just make sure the format of the cells is % and you should be set.

J. Craig Williams

A few more words on this:

If Dependencies are enabled in the sheet, then a formula can not be added to the [% Complete] column.

You lose two things because of this:

1. Visual indication of % Complete in the Gantt chart view (minor to me)

2. Automated roll-up of percent completion to summary rows of your self-calculated percentages.

The last means not only will you need to determine your own % Complete for each child, but also for each parent.

Unless I am counting widgets, then most of my work is not linear so a 1 day = X % does not work for me, but I understand that for some PM's it is a reasonable simplification/assumption.

Craig