What is a formula for % complete to be a percentage based on progress from Start Date to End Date

Paul Newcome

@Jeff Casto The issue is that your Start Date and Duration columns are empty.

Jeff Casto

@Paul Newcome Oh of course! I think I came in with an assumption that the "completed" tasks would be represented by a 100 and not a 1. In that screenshot, I carried the formula down through the tasks and the completed tasks show a number "1". If that is the desired result, then we're good and thank you (thank you either way). I was planning on seeing a 100 to represent 100%, but 1 is fine.

Here's a screenshot showing the formula in the "populated" fields.

Thanks again!

Paul Newcome

@Jeff Casto Percentages are actually a part of a whole (or a part of 1). So 1 = 100%, 0.50 = 50%, so on and so forth. If you click on the column header and then click on the percentage button in the top toolbar, it should display as expected.

Jeff Casto

Thank you Paul! Your time and help is very much appreciated.

Paul Newcome

@Jeff Casto Happy to help. 👍️

MayukhC

https://community.smartsheet.com/discussion/comment/311563#Comment_311563

Hi @Paul Newcome,

Thanks for the brilliant formula, this indeed saves a lot of time and has really been insightful, however, what could I add once my activity is a milestone with (Duration = 0days) and the % complete column throws me the below error.

What modification in the fx i could do fix it, snapshot of my fx is below;

Paul Newcome

@MayukhC You would use an IFERROR.

=IFERROR(original_formula, "output if there is an error")

Fosterme

@Paul Newcome - I tried this out but for some reason it is still showing more than 100%. Can you see what I may be doing wrong?

Paul Newcome

@Fosterme Try wrapping your formula in a MIN statement.

=MIN(existing_formula, 1)

Fosterme

@Paul Newcome thank you! I ended up playing around and ended up here. Seems to be working for the purpose I am looking for!