Expected % Complete MS Project Custom Column

I attended Angela Chellas’ MS Project Advanced workshop today (excellent course) and there was a question about how to figure out what % complete that a task should be if it was on track.  Based on elapsed time, the Expected % Complete for tasks and summary items may be calculated using a MS Project Custom Field.

[expand title=”Text of the formula – to paste in.”]

Int(
  IIf([Current Date]>=[Start]
     ,IIf([Current Date]<=[Finish],
        IIf([Duration]>0,
           ProjDateDiff([Start],[Current Date])/[Duration]*100
           ,IIf([Current Date]>=[Start],100,0))
        ,100)
     ,0)
)

[/expand]

 

You create the custom column like this.

customcol2

Comments

  1. Seems to work great for many of my tasks, but it isn’t calculating correctly on some that are lower on my schedule. I don’t use any resources or costs. Just pure Duration, % Complete, and this new column. The only differences that I see between the ones that work and the ones that don’t are that the summary tasks are indented farther to the right and it’s farther down the schedule (starts with Task #247). Can you help me figure this out?

  2. Just noticed. If all of the summary do not start before the Current Date, it will not calculate correctly. Do you have a good place where I can look up how to understand the equations? They are quite a bit different than excel.