Hi,
I need to convert a formula that I used in MS Project (see below) to calculate task health status that uses the task duration. Has anyone created a similar formula that they could share?
Purpose: Highlight tasks that are running late, based on the actual % complete compared to the “expected completion” using the task duration.
MS Project formula:
IIf([Scheduled Duration]=0,(IIf([% Complete]=100,5,IIf([Scheduled Finish]<Now()+7 And [% Complete]<100 And [% Complete]>=80,3,IIf([Scheduled Finish]<Now()+7 And [% Complete]<80,4,1)))),IIf([% Complete]=100,5,IIf([% Complete]>=100*(Abs(ProjDateDiff([Scheduled Start],Now())/ProjDateDiff([Scheduled Start],[Scheduled Finish]))),2,(IIf([Scheduled Finish]>Now(),IIf([Scheduled Start]>Now(),1,3),4)))))
For zero duration tasks (milestones)
If % Complete = 100, return Blue
If the Planned End Date is at least seven days away, return Clear
Otherwise, if % Complete is < 80, return Red
If % Complete is between 80 and 99, return Yellow
For non-zero duration tasks
If % Complete = 100, return Blue
If it’s not scheduled to start yet (Planned Start Date), return Clear
If it’s past the Planned End date and % Complete <100, return Red
If % complete >= the prorated expected completion, return Green
Otherwise, return Yellow.
That “prorated expected completion” is calculated using the project calendar. If the percent complete is less than the number of days since the task was scheduled to begin, divided by the task duration, it will be Yellow; otherwise, it will be Green.
