Expected % Complete

Bridget Jones

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

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%).