Calculated Status Indicator using task duration
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 nonzero 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.
Best Answers

Lets give something like this a try...
=IF([% Complete]@row = 1, "Blue", IF([Scheduled Duration]@row = 0, IF([Scheduled Finish]@row <= TODAY(7), IF([% Complete]@row< .8, "Red", "Yellow")), IF([Scheduled Start]@row > TODAY(), "", IF([Scheduled Finish]@row < TODAY(), "Red", IF([% Complete]@row>= (TODAY()  [Scheduled Start]@row) / [Scheduled Duration]@row, "Green", "Yellow")))))

My bad! See update below.
For zero duration tasks (milestones)
If % Complete = 100, return Blue
If the Planned End Date is at least seven days away, return
ClearGreen Otherwise, if % Complete is < 80, return Red
If % Complete is between 80 and 99, return Yellow
For nonzero duration tasks
If % Complete = 100, return Blue
If it’s not scheduled to start yet (Planned Start Date), return
ClearGreen If it’s past the Planned End date and % Complete <100, return Red
If % complete >= the prorated expected completion, return Green
Otherwise, return Yellow.

=IF([% Complete]@row = 1, "Blue", IF([Planned Duration]@row = 0, IF([Planned End Date]@row <= TODAY(7), IF([% Complete]@row< .8, "Red", "Yellow"), "Green"), IF([Planned Start Date]@row > TODAY(), "Green", IF([Planned End Date]@row < TODAY(), "Red", IF([% Complete]@row>= (TODAY()  [Planned Start Date]@row) / [Planned Duration]@row, "Green", "Yellow")))))
The above should take of the "Green" updates. I still need to do a little testing in regards to the prorated expected completion issue.
EDIT: Fixed column names. Original formula was based off of what you had posted as your MS Project formula.

=IF([% Complete]@row = 1, "Blue", IF([Planned Duration]@row = 0, IF([Planned End Date]@row <= TODAY(7), IF([% Complete]@row< .8, "Red", "Yellow"), "Green"), IF([Planned Start Date]@row > TODAY(), "Green", IF([Planned End Date]@row < TODAY(), "Red", IF([% Complete]@row >= NETWORKDAYS([Planned Start Date]@row, TODAY()) / [Planned Duration]@row, "Green", "Yellow")))))
Bold portion above should fix the prorated expected completion issue. My initial formula was calculating all days whereas your duration is based off of working days. Adjusting the formula to only count workdays seems to have squared it away. Give it a run through and let me know what you think...
Answers

Do you have the "the prorated expected completion" already calculated in another column, or would this calculation need to be done within the overall formula?

it is not currently calculated in another column, so it would be great to have it calculated in the overall formula, or I could add another column.

Lets give something like this a try...
=IF([% Complete]@row = 1, "Blue", IF([Scheduled Duration]@row = 0, IF([Scheduled Finish]@row <= TODAY(7), IF([% Complete]@row< .8, "Red", "Yellow")), IF([Scheduled Start]@row > TODAY(), "", IF([Scheduled Finish]@row < TODAY(), "Red", IF([% Complete]@row>= (TODAY()  [Scheduled Start]@row) / [Scheduled Duration]@row, "Green", "Yellow")))))

Excellent! One small tweak, I forgot to add that I would like to flag and tasks with a scheduled start greater than today to show green. Thoughts?

Paul  after reviewing further it appears that tasks are being flagged yellow that I did not expect. For example, a 20 day task that is 99% complete is showing yellow. Since today (2/26) is day 18 of 20, I would expect anything over 90% (18/20) to show green. Thoughts? (note I updated your formula with our actual column names)

Which would that be for?
In the Milestones section, you didn't specify, but in the section regarding nonzero durations, you specified to have them as clear or blank.

My most recent question is in regards to the "Green".
I will look into your % Complete concern and get back to you. I have a few meetings to get to. Thanks for your patience. 🙂

My bad! See update below.
For zero duration tasks (milestones)
If % Complete = 100, return Blue
If the Planned End Date is at least seven days away, return
ClearGreen Otherwise, if % Complete is < 80, return Red
If % Complete is between 80 and 99, return Yellow
For nonzero duration tasks
If % Complete = 100, return Blue
If it’s not scheduled to start yet (Planned Start Date), return
ClearGreen If it’s past the Planned End date and % Complete <100, return Red
If % complete >= the prorated expected completion, return Green
Otherwise, return Yellow.

=IF([% Complete]@row = 1, "Blue", IF([Planned Duration]@row = 0, IF([Planned End Date]@row <= TODAY(7), IF([% Complete]@row< .8, "Red", "Yellow"), "Green"), IF([Planned Start Date]@row > TODAY(), "Green", IF([Planned End Date]@row < TODAY(), "Red", IF([% Complete]@row>= (TODAY()  [Planned Start Date]@row) / [Planned Duration]@row, "Green", "Yellow")))))
The above should take of the "Green" updates. I still need to do a little testing in regards to the prorated expected completion issue.
EDIT: Fixed column names. Original formula was based off of what you had posted as your MS Project formula.

=IF([% Complete]@row = 1, "Blue", IF([Planned Duration]@row = 0, IF([Planned End Date]@row <= TODAY(7), IF([% Complete]@row< .8, "Red", "Yellow"), "Green"), IF([Planned Start Date]@row > TODAY(), "Green", IF([Planned End Date]@row < TODAY(), "Red", IF([% Complete]@row >= NETWORKDAYS([Planned Start Date]@row, TODAY()) / [Planned Duration]@row, "Green", "Yellow")))))
Bold portion above should fix the prorated expected completion issue. My initial formula was calculating all days whereas your duration is based off of working days. Adjusting the formula to only count workdays seems to have squared it away. Give it a run through and let me know what you think...

@Paul Newcome you are a rock star! I'm sharing this new status indicator with my team and

Happy to help! 👍️


@ConnectEd You would also need a nested IF formula.
=IF([% Complete]@row = 1, "Complete", IF([Total Slack]@row<= 0, "Critical", ............................
Just work from left to right which ones are highest priority.
Help Article Resources
Categories
Check out the Formula Handbook template!