Calculated Status Indicator using task duration

Melissa Kosiewicz
Melissa Kosiewicz ✭
in Formulas and Functions

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. 

Tags:
· Share on FacebookShare on Twitter

Best Answers

  • Melissa Kosiewicz
    Melissa Kosiewicz ✭
    Answer ✓

    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.

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

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

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!