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!
Answers

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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
This is great! Thank you @Genevieve P. Your guidance is much appreciated!
Help Article Resources
Categories
Check out the Formula Handbook template!