Auto populate Percentage Complete

Hi,

I have a "task" in my sheet which lasts for 3 months. Is there a way to autopopulate the percentage complete based purely on the time passed?

It is not a measure of outputs / delivery but just showing how long an external resource is in place. For example, after 1 month, it would say 30% complete.

Many thanks, Siobhan

Find more posts tagged with

Formulas

Comments

Brett Evans

Siobahn,

=Today()-StartDate will give you the number of days since the start.

=EndDate-Start Date will give you the number of days total for the task

=(Today()-StartDate)/(EndDate-StartDate) will give you the percentage complete.

hope this helps,

Brett

J. Craig Williams

Siobhan,

If your sheet has project Dependencies enabled, the % Complete column can not have a formula in it. This is assuming that the % Complete column (by name) has been chosen as the % Complete column (by function) under the Dependency Settings | Options section.

If you turn this 'off' by selecting --None-- in the dropdown, the summary rows will not auto-calculate the rollup percentage.

The formula provided by Brett will work in another Text/Number column or in the % Complete (by name) column if it is not connected to the Dependency functionality.

Craig

gabsy1972

hello Brett,

thanks for your helpful advise i have used your formula for Percentage complete, however if its a date in the future i get a percentage of -606% help...

Orders Placed 10d 29/06/18 12/07/18 Green -606%

JenValdez

Hi,

I was able to add this formula but for tasks that have already passed, it gives an output of more than 100%. Conversely, for tasks that have not yet reached the start date, it gives a negative percentage. How would I adapt the formula to safeguard against that?

Jen

Becky Swanwick

I would like to know the answer to this too. So far, I've worked around the negative percentage appearing before the start date by using conditional formatting to change the font colour in the relevant cell to the same colour as the cell itself to render it invisible. However, I don't want to do this with completed tasks, I simply want them to keep showing 100%. How do I do this?

Katie2018

Nested IF statements work for this:

=IF([Start Date]24 > TODAY(), "0%", IF([Due Date]24 < TODAY(), "100%", NETWORKDAYS([Start Date]24, TODAY()) / Duration24))

The above formula will return "0%" if the start date is in the future and "100%" if the finish date is in the past. Note I wrote this to leverage the duration column when dependencies are enabled. If dependencies are not enabled, you will need to follow the original formula posted above. Also, NETWORKDAYS will take into account any non-working days to adjust your overall progress accordingly.

Todd Smelser

Katie,

I was able to use your formula (After some customization to my Gantt) and wanted to say thank you!