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

thinkspi.com

• 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?

thinkspi.com

• 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%

• ✭✭✭✭✭✭
edited 03/21/22

@lukasrobbo97 Try this:

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

thinkspi.com

• @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.

thinkspi.com

• 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.

thinkspi.com

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

thinkspi.com