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%”))))
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
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.
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%”)))
Hi, I think there is a problem in the double quotes you have used. See this link http://i.imgur.com/WeFRcXX.png
thanks, it works well when re-typing the double quote
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?
This is very useful information and I am grateful to you Sai for posting this- very useful
You are the best
Thanks for the great post
The only problem i have when i use a (Milestone Task)
gives me #ERROR …. Why???
Thank you
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%”)))
Hi,
I get an error with any 0d activity, any tips on why or how to fix?
I’m using Project 2016.
Thanks!
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%”)))
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,
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%”))))
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.
Copying and pasting the formula to Project doesn’t work as the quotes are not pasted right. Please retype the double and single quotes.
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
#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
Solved. Thank you for your assistance. Really appreciate it 🙂
Hello Sai,
Is it possible to make a variance column equals % Expected(Planned) – % Physical Complete? Similar to Zaid Iskandar question but only difference is use %Physical Complete instead of %Complete
Tardu – You can do it.
1. Insert Number1 field (to hold the Expected % complete)
IIf([Status Date][Baseline Finish],[Baseline Duration],Abs(ProjDateDiff([Baseline Start],[Status Date]))))
2. Insert Number 2 field (to compare Expected % complete with Physical % complete)
[Number1]-[Physical % Complete]
By the way, the expected % complete is based on duration but Physical % complete is based on real work. Why do you want to compare them?