Percentage (%) complete formula

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?

Find more posts tagged with

Formulas

Project Management

Comments

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

Percentage Format toggle.PNG

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?

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…