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!

Find more posts tagged with

Accepted answers

All comments

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

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.