Percentage (%) complete formula

Becky Swanwick

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

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

=MIN( your formula, 1)

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

Craig

 

Becky Swanwick

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

Becky Swanwick

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

J. Craig Williams

Glad you got what you wanted Becky.

Craig

Matt McC.

Thanks you!

Matt McC.

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

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

THANK YOU!  This is exactly what I needed.

Ray G

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

 

Any Suggestions?

Lota

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))))))

César

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

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

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

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

Todd Smelser

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

Any hints?