Percent Complete Plan Formula
Smartsheet Community:
I am Tracking Plan VS Actual and have a % Complete Formula to Track The Plan. However, My Formula is at 100% Complete for Plan, but the due date is 11/28/22. Therefore, it should be Calculating 100% Complete as of Today. Does anyone have any idea where in my formula is incorrect?
% Complete (Plan): =IF(IFERROR(IF(AND(TODAY() >= [Start Date (Actual)]@row, TODAY() <= [Finish Date (Actual)]@row), ROUND(NETWORKDAYS([Start Date (Actual)]@row, TODAY()) / [Duration (Actual)]@row, 2), IF(TODAY() > [Finish Date (Actual)]@row, 1, 0)), 0) > 1, 1, IFERROR(IF(AND(TODAY() >= [Start Date (Actual)]@row, TODAY() <= [Finish Date (Actual)]@row), ROUND(NETWORKDAYS([Start Date (Actual)]@row, TODAY()) / [Duration (Actual)]@row, 2), IF(TODAY() > [Finish Date (Actual)]@row, 1, 0)), 0))
Thanks
Answers
-
Seems overly complicated mate.
=IF([Start Date Actual]@row>today(),0,IF([End Date Actual]@row<Today,1,(NETWORKDAYS([Start Date (Actual)]@row, [Finish Date (Actual)])/NETWORKDAYS([Start Date (Actual)]@row, Today()))
-
I Received UNPARSEABLE
-
OK, so where is the mistake?
-
=IF([Start Date Actual]@row > TODAY(), 0, IF([End Date Actual]@row < TODAY(), 1, (NETWORKDAYS([Start Date Actual]@row, TODAY()) / NETWORKDAYS([Start Date Actual]@row, [End Date Actual]@row))))
THis formula might also return unparseable, any idea why?
-
FYI: I Figured Out The Problem. The Formula has an Issue with Days that are in Fractions. Fir Example my Kick-Off which is the Processor was only 0.125d. Once I changed that to 1d. The % Complete Plan Adjusted to 95% Complete
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 459 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 137 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives