Calculated Status Indicator using task duration

Melissa Kosiewicz

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. 

Paul Newcome

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")))))

Melissa Kosiewicz

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 Clear Green

 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 Green

 If it’s past the Planned End date and % Complete <100, return Red

 If % complete >= the prorated expected completion, return Green

 Otherwise, return Yellow.

Paul Newcome

=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.

Paul Newcome

=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

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?

Melissa Kosiewicz

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.

Paul Newcome

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")))))

Melissa Kosiewicz

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?

Melissa Kosiewicz

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)

Paul Newcome

Which would that be for?

In the Milestones section, you didn't specify, but in the section regarding non-zero durations, you specified to have them as clear or blank.

Paul Newcome

@Melissa Kosiewicz

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. 🙂

Melissa Kosiewicz

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 Clear Green

 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 Green

 If it’s past the Planned End date and % Complete <100, return Red

 If % complete >= the prorated expected completion, return Green

 Otherwise, return Yellow.

Paul Newcome

=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.

Paul Newcome

=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...

Melissa Kosiewicz

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

Paul Newcome

Happy to help! 👍️

ConnectEd

Hello Paul,

@Paul Newcome I need a similar help

Paul Newcome

@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.