Automatic Linear Percentage Complete Formula

lukasrobbo97 · 03/18/22

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

Paul Newcome · 03/18/22

You are going to want something like this...

=(TODAY() - [Actual Start Date]@row) / ([Baseline End Date]@row - [Actual Start Date]@row)

lukasrobbo97 · 03/18/22

https://community.smartsheet.com/discussion/comment/323119#Comment_323119

Hi Paul, thank you for your contribution. Unfortunately it returns unparseable:(

Paul Newcome · 03/18/22

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?

lukasrobbo97 · 03/18/22

Thank you so much. Seems to have worked. If actual start is blank I need it to show 0 tho

lukasrobbo97 · 03/18/22

https://community.smartsheet.com/discussion/comment/323129#Comment_323129

@Paul Newcome are you able to take a second look please. Also I would like to max out at 100%

Paul Newcome · 03/21/22

@lukasrobbo97 Try this:

=IFERROR(MIN(1, (TODAY() - [Actual Start Date]@row) / ([Baseline End Date]@row - [Actual Start Date]@row)), 0)

lukasrobbo97 · 03/21/22

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

Paul Newcome · 03/21/22

It looks like you may have missed a closing parenthesis after the first [Actual Start]@row.

lukasrobbo97 · 03/21/22

Hi @Paul Newcome

When it is like this it only returns 0

=IFERROR(MIN(1, (TODAY() - [Actual Start]@row / ([Baseline Finish]@row - [Actual Start]@row))), 0)

Paul Newcome · 03/21/22

Right. Because the missing closing parenthesis goes after the FIRST [Actual Start]@row, not the second.

lukasrobbo97 · 03/22/22

Hi @Paul Newcome

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

Paul Newcome · 03/22/22

Try this...

=IFERROR(MAX(MIN(1, (TODAY() - [Baseline Start]@row) / ([Baseline Finish]@row - [Baseline Start]@row)), 0), 0)

Automatic Linear Percentage Complete Formula

Answers

Categories