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

Find more posts tagged with

Sheets

Formulas

functionality

Project Management

Cell linking

Accepted answers

All comments

Paul Newcome

You are going to want something like this...

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

lukasrobbo97

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

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

Paul Newcome

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

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

lukasrobbo97

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

@lukasrobbo97 Try this:

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

lukasrobbo97

@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

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

lukasrobbo97

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

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

lukasrobbo97

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

Try this...

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.