Percentage (%) complete formula

Becky Swanwick · 08/09/18

I'm using this formula suggested in another thread to calculate the percentage complete for particular tasks:

=(Today()-StartDate)/(EndDate-StartDate)

What I'd like to know is how to cap this at 100% because I don't want the percentage to keep increasing the further past the completion date we go. Any ideas?

Eric Peery · 08/09/18

This works in my sheet

=IF(((TODAY() - [Start Date]17) / ([End Date]17 - [Start Date]17)) < 1, ((TODAY() - [Start Date]17) / ([End Date]17 - [Start Date]17)) * 100, 100)

J. Craig Williams · 08/09/18

=MIN( your formula, 1)

Watch out for divide by zero when the task is completed the same day it started.

Craig

Becky Swanwick · 08/10/18

Thanks for replying, Eric, I appreciate you taking the time.

Becky Swanwick · 08/10/18

Thanks, Craig, this is exactly what I was looking for.

J. Craig Williams · 08/10/18

Glad you got what you wanted Becky.

Craig

Matt McC. · 02/13/19

Thanks you!

Matt McC. · 02/13/19

This formula is great for capping the % at 100 but is there a way to ALSO stop it from going negative?

I've used the formula below to stop it from going negative but having issues combing the 2.

=MAX((TODAY() - Start27) / (Finish27 - Start27), 0)

Any thoughts would be much appreciated!

J. Craig Williams · 02/13/19

Try

=MIN(MAX((TODAY() - Start27) / (Finish27 - Start27), 0),1)

I prefer

=MIN(MAX((TODAY() - Start@row) / (Finish@row - Start@row), 0),1)

Craig

Sarah McMorrow · 03/12/19

THANK YOU! This is exactly what I needed.

Ray G · 03/19/19

When I paste this formula into my sheet it reads " #UNPARSEABLE"?

Any Suggestions?

Lota · 03/21/19

I had just composed a formula for this before I stumbled upon this thread. if anyone else needs the same, hope it helps.

Note: This formula assumes you have a "Start Date" and "End Date" column in your sheet, and that the % complete column is set to a percentage format. (see attached screenshot.

=IF(TODAY() > [End Date]@row, 1, IF(TODAY() < [Start Date]@row, 0, ((NETWORKDAY([Start Date]@row, TODAY()) / (NETWORKDAY([Start Date]@row, [End Date]@row))))))

Percentage Format toggle.PNG

César · 11/08/19

this one worked for me

=IF([Fecha de finalización]@row < TODAY(); 1; IF([Fecha de inicio]@row > TODAY(); 0; (NETWORKDAY([Fecha de inicio]@row; TODAY()) / Duración@row)))

veenz20 · 03/30/20

Hello J. Craig Williams,

I found your formula below very useful. How do I modify it to calculate the percentage of the course completed at a given cancellation date which is anywhere between the start and end date.

=MIN(MAX((TODAY() - Start@row) / (Finish@row - Start@row), 0),1)

veenz20

Kris Robinson · 12/03/20

https://community.smartsheet.com/discussion/comment/149776#Comment_149776

Thank you for this answer. It solved my issue perfectly!

Todd Smelser · 03/22/21

Trying these formulas and Today() seems to not work for me. The Parens turn pink and I get an #UNPARSEABLE error.

Any hints?

Percentage (%) complete formula

Comments

Help Article Resources

Categories