Expected % Complete

I'm attempting to have an "expected % complete" column, and have it working correctly using this formula

=IF([Start Date]@row < TODAY(), (TODAY() - [Start Date]@row) / ([Finish Date]@row - [Start Date]@row), 0)

However, I want the max return value to be 100% (I.e., not 145% if the finish date is in the past). How do I calculate that? I'm at a loss...

Thank you!


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Bridget Jones

    Try this

    =IF([Start Date]@row < TODAY(), MIN((TODAY() - [Start Date]@row) / ([Finish Date]@row - [Start Date]@row), 1), 0)

    @Paul Newcome explains in his post here

    Paul Newcome ✭✭✭✭✭ 12/23/20

    So you would want something along the lines of...

    =MIN((TODAY() - [Start Date]@row) / Duration@row, 1)

    We start with

    (TODAY() - [Start Date]@row) / Duration@row

    to give us the planned % complete. We don't want it to go over 100% though, so we use the MIN function to essentially cap it at 1 (which equals 100%).

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!