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
