How to calculate the expected % complete at a particular date?

Afternoon,

I am trying to illustrate some expected % vs actual % completed, using two dates.

So far I have input the below which based off today shows the task should be 42% complete.

=IF([Start Date]@row <= TODAY(), NETWORKDAYS([Start Date]@row, MIN(TODAY(), [End Date]@row)) / [Duration Between Start & End Date]@row, 0)

When I build out this formula to see the expected % complete as at 30 June, I would expect this to be 100% as in line with the end date being 30/06/24. However it comes out at 71.8%.

Can you point out where I am going wrong please?

Thanks in advance

Find more posts tagged with

Accepted answers

KPH

Hi @JamesQ4

It looks like your formula in Duration Between Start and End Date is not NETWORKDAYS.

My thinking is....

6/30/24 minus 1/1/24 is 181 days. I think this is what you are doing.

1/1/24 to 6/30/24 assuming 5 day weeks and no holidays is 130 net work days. And it looks like these assumptions are valid.

Because 130/181= 71.8% which is suspiciously similar to what you are seeing.

So I think you are including weekends in the denominator but not the numerator. Try changing the formula in Duration Between Start and End Date to use NETWORKDAYS. This will also increase the % complete for TODAY() as well.

All comments

KPH

Hi @JamesQ4

It looks like your formula in Duration Between Start and End Date is not NETWORKDAYS.

My thinking is....

6/30/24 minus 1/1/24 is 181 days. I think this is what you are doing.

1/1/24 to 6/30/24 assuming 5 day weeks and no holidays is 130 net work days. And it looks like these assumptions are valid.

Because 130/181= 71.8% which is suspiciously similar to what you are seeing.

JamesQ4

Thank you @KPH, this has helped resolve the issue, really appreciate it!!

KPH

Glad that resolved it. Thanks for letting know.

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.