Task Health Calculation using duration and % complete

I am working on a formula to calculate task health with R/Y/G harvey balls based on the duration of a task and the % complete. I have found several community discussions around this but the ones I have found , I have not been able to successfully tweak to work in my sheet.

In my sheet, I have Start Date, End Date, Duration, and % Complete columns. Here is a formula I've been testing with. Some row it shows up correctly and others show Invalid Data Type.

=IF(AND([Start Date]@row < TODAY(ROUND(Duration@row / 2)), [% Complete]@row >= 0.5), "Green", IF(AND([Start Date]@row > TODAY(Duration@row * 0.5), [% Complete]@row < 0.5), "Yellow", IF(AND([Start Date]@row > TODAY(Duration@row * 0.75), [% Complete]@row < 0.75), "Red")))


Can anyone see what I am missing here or potentially a better/different formula to use?


Here is the criteria, I am trying to use:

If today is less than or equal to 50% of the task duration and % complete is more than or equal to 50%, return green.

If today is more than 50% of the task duration and less than 75% of the task duration and % complete is less than 50%, return yellow.

If today is more than or equal to 75% of the task duration and the % complete is less than or equal to 75%, return red.


Kelly Pratt

Solution Consultant

Echo Consulting

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I usually use the expected % complete compared to the actual to allow for a little more flexibility/accuracy.


    Expected % Complete:

    =(TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row))


    Using this you can do an actual comparison. Generally speaking I use the following logic:

    On Track/Ahead of Schedule = "Green"

    Behind Schedule by less than 5% = "Yellow"

    Behind Schedule by more than 5% = "Red"

    Complete = "Blue"


    =IF([% Complete]@row = 1, "Blue", IF((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row))>= [% Complete]@row, "Green", IF((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row))>= [% Complete]@row - 0.05, "Yellow", "Red"))))

  • Omid
    Omid ✭✭

    Hi Paul,

    I have columns "% Complete", "Start" and "Finish". I have adjusted your formula to match my columns and I can't seem to get it working. Can you see if I'm doing anything wrong?


    =IF([% Complete]@row = 1, "Green", IF((TODAY() - [Start]@row) / ([Finish]@row - [Start]@row))>= [% Complete]@row, "Blue", IF((TODAY() - [Start]@row) / ([Finish]@row - [Start]@row))>= [% Complete]@row - 0.05, "Yellow", "Red"))))

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Omid

    It looks like there may be some extra parentheses in here. I've bolded the areas where you should only have one closing parentheses:

    =IF([% Complete]@row = 1, "Green", IF((TODAY() - Start@row) / (Finish@row - Start@row) >= [% Complete]@row, "Blue", IF((TODAY() - Start@row) / (Finish@row - Start@row) >= [% Complete]@row - 0.05, "Yellow", "Red")))

    Then make sure you only have 3 at the very end of the entire formula.

    Cheers!

    Genevieve

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Genevieve P. Good catch! Looks like I fat fingered an extra one in mine.


    @Kelly Pratt Please see Genevieve's response.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!