none
MS Projects RRS feed

  • Question

  • Hi All,

    I am trying to calculate the planned progress and using the following formulas:

    Duration in Days (Number 1): Val(ProjDurConv(Duration,pjDays))

    Elapsed Days (Number 2): IIf(ProjDateDiff(Start,[Status Date])/480>=Val(ProjDurConv([Baseline Duration],pjDays)),Val(ProjDurConv([Baseline Duration],pjDays)),IIf(DateDiff("d",[Baseline Start],[Status Date])<=0,0,ProjDateDiff(Start,[Status Date])/480))

    Planned Progress (Text 3): Number2/Number1

    % Planned Progress (Text 2): Format(Text3,"0%")

    When I check the status on a future date, the % planned shows for example 40%. However to test if the formula is working well, I input the same values for % complete as shown by % planned progress but the overall progress is different. For example if % planned is 14% on 12/6/2020 with 4 sub tasks shown as 100%. When I report 100% for the same sub tasks, % complete shows 16%. If I work with more sub tasks lets say 10, the % complete shows either higher or lower than the % planned. Can someone please help.

      

    Saturday, June 13, 2020 11:04 AM

All replies

  • Jabran123,

    The %Complete of a task is Actual Duration/Duration.  The %Complete of a summary task is the summation of the Actual Durations of its non-summary subtasks divided by the summation of the Durations of the subtasks.  This means that your two number fields need to use "Rollup: Sum" for summary row calculation, while your text formulas need "Use formula" for summary row calculation.

    Besides that, your number formulas don't look right to me.  Planned progress should be derived from Baseline values only - i.e. your use of Duration and Start (without the "Baseline" prefix) seem incorrect. I would use a simpler formula for "Planned Actual Duration as of the Status Date" (i.e. your Number2). Namely, the actual duration of the task on the Status Date should be either 1*Baseline Duration (i.e. complete), or 0*Baseline Duration (i.e. not started), or some intermediate value, depending on the difference between the Status Date and the task's Baseline Start and Baseline Finish:  i.e.

    IIf(ProjDateDiff([Baseline Finish],[Status Date])>0,1,IIf(ProjDateDiff([Status Date],[Baseline Start])>0,0,ProjDateDiff([Baseline Start],[Status Date])/[Baseline Duration]))*[Baseline Duration]

    Finally, "Elapsed Days" is kind of a magic word in Project, different from how you've used it.  I'd suggest changing your field name to avoid confusion.  Good luck, tom

     

    Sunday, June 14, 2020 8:59 PM
  • Hi Tom,

    So thank so much for your advise. I started off with the "Rollup:Sum" for the number fields and the issue was sorted. Will check progress status at future dates and confirm that there is no issues. 

    Thank you soo much.


    Monday, June 15, 2020 11:42 AM
  • Glad you got it sorted.
    Monday, June 15, 2020 1:41 PM