Automatic Linear Percentage Complete Formula
Hello,
I am wondering if you can help.
I currently use Smartsheet for project planning.
I would like to build an automatic percentage complete formula. This formula should work off a linear progression line from Actual Start Date to Baseline Finish Date and the duration.
So an example of a task would be:
Actual Start: Now
Baseline finish: 10 Weeks from now
It is now week 6 exactly, then we should be 60% Complete.
Thank you,
Luke
Answers
-
You are going to want something like this...
=(TODAY() - [Actual Start Date]@row) / ([Baseline End Date]@row - [Actual Start Date]@row)
-
Hi Paul, thank you for your contribution. Unfortunately it returns unparseable:(
-
Have you double checked to ensure the column names match what is actually used in your sheet?
Are you able to provide a screenshot of the formula actually in the sheet similar to the screenshot below?
-
Thank you so much. Seems to have worked. If actual start is blank I need it to show 0 tho
-
@Paul Newcome are you able to take a second look please. Also I would like to max out at 100%
-
@lukasrobbo97 Try this:
=IFERROR(MIN(1, (TODAY() - [Actual Start Date]@row) / ([Baseline End Date]@row - [Actual Start Date]@row)), 0)
-
@Paul Newcome It comes back saying incorrect argument set. Can you see any errors?
=IFERROR(MIN(1, (TODAY() - [Actual Start]@row / ([Baseline Finish]@row - [Actual Start]@row)), 0)
-
It looks like you may have missed a closing parenthesis after the first [Actual Start]@row.
-
When it is like this it only returns 0
=IFERROR(MIN(1, (TODAY() - [Actual Start]@row / ([Baseline Finish]@row - [Actual Start]@row))), 0)
-
Right. Because the missing closing parenthesis goes after the FIRST [Actual Start]@row, not the second.
-
This is the formula im using. I'm now doing it off the baseline start and finish:
=IFERROR(MIN(1, (TODAY() - [Baseline Start]@row) / ([Baseline Finish]@row - [Baseline Start]@row)), 0)
Thank you for your ongoing support. It seems to have got more cells working. But I'm not sure why if the date is in the future it appears as a minus whereas it should appear as 0%.
Thank you for all your help :)
-
Try this...
=IFERROR(MAX(MIN(1, (TODAY() - [Baseline Start]@row) / ([Baseline Finish]@row - [Baseline Start]@row)), 0), 0)
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives