Task Completion Percentages

parulmishra

So I have a pretty basic Smartsheet tracker with set of Goals (parent) and group of initiatives(children) taken to fulfill the required goal. I am looking for ways on how to calculate the Completion percentages of goals...

I was looking for something in combination of Time bound, No of child initiatives completed..

Is it possible in Smartsheet's or what other metric we can use to calculate the percentage completion..

I know its a pretty generic but I am looking forward for ideas and any inputs are most welcome

Hollie Green

I figured out a way to make the 2nd formula not show up as over 100% if the due date is past and it is still not completed. That was the reason for the manual update of the due date on the second option is it would move into over 100%

=IF(AND([Action Items Due Date]@row <= TODAY(), Status@row <> "Complete"), 0.99, IF(Status@row <> "Complete", NETWORKDAYS([Action Item Date Assigned]@row, TODAY()) / NETWORKDAYS([Action Item Date Assigned]@row, [Action Items Due Date]@row), 1))

parulmishra

@Hollie Green and @Genevieve P. Thank you... so much for your inputs... let me try it

Hollie Green

There are a few ways you can do it. Below are a couple that I have used in the past.

Option 1

You can have where you manually mark the % complete for the child rows and use the % complete from the project settings menu to update the parent row

Option 2 - This option only works if your projects are completed before the due date I rarely use this option unless I know each section will not have delays or I will be manually updating the due date when there are delays.

Put in a formula in your child rows to get the % complete based on how many days are remaining of the action item.

=If(Status@row="",NETWORKDAYS([Action Item Date Assigned]@row, TODAY()) / NETWORKDAYS([Action Item Date Assigned]@row, [Action Items Due Date]@row),100%)

Then do =AVG(CHILDREN()) to get an overall average of the %complete of the child rows.

parulmishra

I should have been more precise here...

I am not looking for a manual update on the completion % be team members..

Genevieve P.

Hi @parulmishra

Did you try option 2? @Hollie Green's second option contains formulas that would automatically calculate percentage for you, although I may suggest updating the formula so that you use 1 instead of 100% since formulas need percentage in decimals.

Try:

=IF(Status@row="", NETWORKDAYS([Action Item Date Assigned]@row, TODAY()) / NETWORKDAYS([Action Item Date Assigned]@row, [Action Items Due Date]@row), 1)

You can adjust this based on what your Status dropdowns are, so if you only want it to be 100% if the Status says "Complete", then change the beginning statement:

=IF(Status@row <> "Complete", NETWORKDAYS([Action Item Date Assigned]@row, TODAY()) / NETWORKDAYS([Action Item Date Assigned]@row, [Action Items Due Date]@row), 1)

Cheers,

Genevieve

Hollie Green

I figured out a way to make the 2nd formula not show up as over 100% if the due date is past and it is still not completed. That was the reason for the manual update of the due date on the second option is it would move into over 100%

=IF(AND([Action Items Due Date]@row <= TODAY(), Status@row <> "Complete"), 0.99, IF(Status@row <> "Complete", NETWORKDAYS([Action Item Date Assigned]@row, TODAY()) / NETWORKDAYS([Action Item Date Assigned]@row, [Action Items Due Date]@row), 1))

parulmishra

@Hollie Green and @Genevieve P. Thank you... so much for your inputs... let me try it