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
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions

This is great! Thank you @Genevieve P. Your guidance is much appreciated!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!