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?
Comments
-
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)
-
=MIN( your formula, 1)
Watch out for divide by zero when the task is completed the same day it started.
Craig
-
Thanks for replying, Eric, I appreciate you taking the time.
-
Thanks, Craig, this is exactly what I was looking for.
-
Glad you got what you wanted Becky.
Craig
-
Thanks you!
-
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!
-
Try
=MIN(MAX((TODAY() - Start27) / (Finish27 - Start27), 0),1)
I prefer
=MIN(MAX((TODAY() - Start@row) / (Finish@row - Start@row), 0),1)
Craig
-
THANK YOU! This is exactly what I needed.
-
When I paste this formula into my sheet it reads " #UNPARSEABLE"?
Any Suggestions?
-
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))))))
-
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
-
Thank you for this answer. It solved my issue perfectly!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!