Project Plan Template Formula Issue

Nathan Umbriac

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!

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!