# Percentage (%) complete formula

Options
edited 12/09/19

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?

«1

• Options

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)

• ✭✭✭✭✭✭
Options

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

Craig

• Options

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

• Options

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

• ✭✭✭✭✭✭
Options

Glad you got what you wanted Becky.

Craig

• Options

Thanks you!

• Options

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!

• ✭✭✭✭✭✭
Options

Try

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

I prefer

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

Craig

• Options

THANK YOU!  This is exactly what I needed.

• Options

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

Any Suggestions?

• Options

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

• ✭✭
Options

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

• Options

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

• Options

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

• ✭✭✭
Options

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

Any hints?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!