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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

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


    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%

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    thinkspi.com

  • 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
    Paul Newcome ✭✭✭✭✭✭

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

    thinkspi.com

  • 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
    Paul Newcome ✭✭✭✭✭✭

    Try this...


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

    thinkspi.com