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
-
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.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!