Project Plan Template Formula Issue

I have a question on a formula used in a project plan template currently being used.

Many times, the % complete (expected) is greater than 100%. The formula currently being used is Expected Effort Complete/Cumulative Effort.

The Cumulative Effort is the duration of the task, in days, and the expected effort complete formula is IF(TODAY() > [Planned Finish]@row, Duration@row, IF(TODAY() >= [Planned Start]@row, TODAY() - [Planned Start]@row, 0)))

I cannot figure out where the formula error is that is giving me a greater than 100% expected complete figure.

Thanks!

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

Hi @Nathan Umbriac

Your calculation of the % complete (expected) will be greater than 100% if the expected effort complete formula outputs a number greater than the duration of the task.

I believe this is happening since your formula is simply subtracting Today from the Start Date without taking into consideration work days.

For example, if your Start Date was on a Friday and Today was Monday, then TODAY() - [Planned Start]@row would equal 4 days, but the Duration would actually be 2 working days. Does that make sense?

To adjust this, you'll want to use the NETWORKDAYS function in your formula, like so:

=IF(TODAY() > [Planned Finish]@row, Duration@row, IF(TODAY() >= [Planned Start]@row, NETWORKDAYS([Planned Start]@row, TODAY()), 0))

Let me know if this resolves your issue!

Cheers,

Genevieve

Nathan Umbriac

This is great! Thank you @Genevieve P. Your guidance is much appreciated!

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.