How to calculate planned % Complete?


You have updated the work progressed in % complete field, but there is no Project field that will give you the planned % based on the status date.

Create a custom field using a formula calculation the planned % complete, based on the status date.
1. Field : Expected minutes to status date (Number1)
– Insert a Number1 field in Gantt Chart.
– Right Number1 field and choose Custom Fields.
– Under Custom attributes, click Formula and type the below text
IIf([Status Date]<[Baseline Start],0,IIf([Status Date]>[Baseline Finish],[Baseline Duration],Abs(ProjDateDiff([Baseline Start],[Status Date]))))
– Under Calculation for tasks and group summary rows, choose Rollup with dropdown as sum
2. Field : Expected % complete (Text1)
– Insert a Text1 field in Gantt Chart.
– Right Text1 field and choose Custom Fields.
– Under Custom attributes, click Formula and type the below text
IIf([Status Date]=ProjDateValue(‘NA’),”No Status Date”,IIf([Baseline Start]=ProjDateValue(‘NA’),”No BL”,IIf([Baseline Duration]>0,FORMAT([Number1]/[Baseline Duration],”0%”),IIf([Milestone]=Yes And ProjDateDiff([Baseline Start],[Status Date])>0,”100%”,”0%”))))

Advertisements

19 thoughts on “How to calculate planned % Complete?”

  1. Dear Sai,

    Thanks for sharing valuable information.
    I get a syntax error message when running “Expected % Complete”
    System highlights ‘NA’ as an error.

    Please advise

    Regards,
    NS Pillai

  2. Dear NS Pillai – This works except the ‘ (single quotes) and ” (double quotes) when copied from this blog post is replaced with a different symbols. I request you to replace the single and double quotes with the correct symbols.

    1. Dear Sai,

      Please advise what you mean by correct symbols.
      I corrected the formula as below still I get syntax error message:

      IIf([Status Date]=ProjDateValue(”NA”),”No Status Date”,IIf([Baseline Start]=ProjDateValue(”NA”),”No BL”,FORMAT([Number1]/[Baseline Duration],”0%”)))

  3. Hi,

    I followed the instructions as stated above but for field 2: Expected % complete (Text1), I only get No Status Date for all tasks.

    Please advise?

  4. Thanks for the great post
    The only problem i have when i use a (Milestone Task)
    gives me #ERROR …. Why???

    Thank you

    1. There is problem with Text1 formula. Thanks for pointing this out. When the baseline duration is 0, the divide by zero error occured. The corrected formula is IIf([Status Date]=ProjDateValue(‘NA’),”No Status Date”,IIf([Baseline Start]=ProjDateValue(‘NA’),’No BL’,IIf([Baseline Duration]>0,FORMAT([Number1]/[Baseline Duration],”0%”),”0%”)))

    1. There is problem with Text1 formula. Thanks for pointing this out. When the baseline duration is 0, the divide by zero error occured. The corrected formula is IIf([Status Date]=ProjDateValue(‘NA’),”No Status Date”,IIf([Baseline Start]=ProjDateValue(‘NA’),’No BL’,IIf([Baseline Duration]>0,FORMAT([Number1]/[Baseline Duration],”0%”),”0%”)))

      1. Hello,

        Thanks so much for your response, i really appreciate it.

        The above has fixed the #error i was getting for 0d duration tasks, however, the Planned % complete is sitting at 0% for tasks that should be 100% by now (i.e. they started 11/10/17 and finish 27/12/2017). I have baselined and the current status date is the 12/1/2018. Or tasks that should be 50% complete are showing as 0% in the planned column

        Again, any help would be most appreciated.

        Thanks,
        Erica,

      2. Sorry. My updated formula did not check if the task is a milestone or not. Please use this one instead IIf([Status Date]=ProjDateValue(‘NA’),”No Status Date”,IIf([Baseline Start]=ProjDateValue(‘NA’),”No BL”,IIf([Baseline Duration]>0,FORMAT([Number1]/[Baseline Duration],”0%”),IIf([Milestone]=Yes And ProjDateDiff([Baseline Start],[Status Date])>0,”100%”,”0%”))))

  5. Hi Sai

    2016 does not seem to be accepting the formula as copied in directly.

    It highlights the formula from “No status” to the end.

    1. Copying and pasting the formula to Project doesn’t work as the quotes are not pasted right. Please retype the double and single quotes.

  6. Hello sir. I’ve managed to create the “Expected %” and it really helps for my project. Is there a way to calculate variance between actual “% Complete” and “Expected %”? I’ve tried to create a custom field by inserting simple formula of subtracting both column, however it results an #ERROR. Hopefully my comment reach your attention. Thank you

    1. #ERROR is due the data type mismatch. % Complete is numeric while Expected % [Custom Field] is text. To know the difference you need to subtract % Complete with number field. Insert the Number2 field, and set the formula to IIf([Status Date]=ProjDateValue(‘NA’),-1,IIf([Baseline Start]=ProjDateValue(‘NA’),-1,IIf([Baseline Duration]>0,CInt([Number1]/[Baseline Duration]*100),IIf([Milestone]=Yes And ProjDateDiff([Baseline Start],[Status Date])>0,100,0)))). Ensure the same formula is rolled up to the summary tasks too

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s