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

INDEX / MATCH / CONTAINS Assistance
Looking for your help, the below formula works for the moment: =IFERROR(INDEX({SFDC Opps for Renewals Range 1}, MATCH(SFOP@row , {SFDC Opps for Renewals Range 2}, 0)), "No Match Found") However the column above actually contains multiple SFOPs and so I think I need to add a CONTAIN element to the formula so when SFOP…
Writing Formula for Sheet Summary Field
Based on the screenshot below, I am trying to write a formula that will pull in the reason for change on the row where " most recent change" is checked. Thoughts on the best formula? I've tried: =IF([Most Recent Change]:[Most Recent Change] = 1, [Reason for Change]:[Reason for Change] , "") Is there something I can do with…
Help with this Formula
I'm trying to seta formula for the health of the project's tasks based on certain conditions. This is the formula: =IF(OR(AND(Status@row <> "Complete", [End Date]@row < TODAY()), AND(Status@row = "In Progress", VALUE([% Complete]@row ) < 0.3, NETDAYS(TODAY(), [End Date]@row ) <= 30), AND(Status@row = "Not Started", [Start…