# Calculated Status Indicator using task duration

Options

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

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

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.

Tags:

Options

 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

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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?

• Options

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.

• Options

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?

• edited 02/26/20
Options

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)

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

Options

 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

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

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

• Options

Hello Paul,

@Paul Newcome I need a similar help

• ✭✭✭✭✭✭
Options

@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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!