Project Health Formula

Hi there,

My formula seems to be broken. Any ideas?

=IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), ISBLANK([Projected End Date]@row)), "Yellow", IF(Status@row = "Complete", "Green", IF((TODAY() - [Projected End Date]@row > 0), "Red", "")))

  • Project Health turns "Green" for any row that has a Status of "Complete" or if the Projected End Date is 1 week out or further
  • Project Health turns "Yellow" for any row that does not have a Status of "Complete", "On Hold", or "Canceled", and the Projected End Date is within the next 7 days
  • Project Health turns "Gray" for any row that has a Status of "On Hold" or "Canceled"
  • Project Health turns "Red" for any row that does not have a Status of "Complete", "On Hold", or "Canceled", and the Projected End Date is within the next 3 days or is in the past
  • Project Health is “blank” for any row where the Status and Target End Date cells are empty
  • Additionally, it should show the average health of its children's tasks.


Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Ok great! Add this to your formula then:

    IF(Status@row = "at risk", "Red"


    I would suggest putting it at the beginning of your child row statements so that this will be the first criteria looked at:

    =IF(COUNT(ANCESTORS(Task@row)) = 0, IF(AND([Start Date]@row = "", [Projected End Date]@row = ""), "Gray", IF(AND([% Complete]@row < 0.5, [Projected End Date]@row < TODAY(30)), "Red", IF(AND([% Complete]@row < 0.75, [Projected End Date]@row < TODAY(30)), "Yellow", IF(OR([% Complete]@row > 0.75, [Projected End Date]@row >= TODAY(30)), "Green")))), IF(Status@row = "at risk", "Red", IF(OR(Status@row = "On Hold", Status@row = "Cancelled"), "Gray", IF(AND([Projected End Date]@row = "", Status@row = ""), "", IF(OR(Status@row = "Complete", [Projected End Date]@row > TODAY(7)), "Green", IF((TODAY() - [Projected End Date]@row > 0), "Red", IF(OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = ""), "Yellow", "")))))))

    Cheers,

    Genevieve

Answers

  • @Paul Newcome The green project health one is the only one working. The rest are not, and the parent child relationship is not reflected in any.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    How would you expect the child average to work? What if there are two red and two green? Can you spell out in detail how that piece would work similar to the level of detail you provided for the child rows in your original post?

  • @Paul Newcome

    The parent row will remain green unless, it has one or more children rows with yellow or red categories.

    • Project Health turns "Green" for any row that has a Status of "Complete" or if the Projected End Date is 1 week out or further
    • Project Health turns "Yellow" for any row that does not have a Status of "Complete", "On Hold", or "Canceled", and the Projected End Date is within the next 7 days
    • Project Health turns "Red" for any row that does not have a Status of "Complete", "On Hold", or "Canceled", and the Projected End Date is within the next 3 days or is in the past

    Ideally, I'd also have a overall project health formula so I can feed into a dashboard.

    Looking at row 1 (start and projected end date):

    Green if the overall project completion % is <75, within 1 month of projected end date.

    Yellow if the overall project completion % is <50, within 1 month of projected end date.

    Red if the overall project completion % is >50, within 1 month of projected end date.

    Gray if the project start date and end date is blank.



  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @ecarrero

    Try this:

    =IF(COUNT(ANCESTORS(Task@row)) = 0, IF(AND([Start Date]@row = "", [Projected End Date]@row = ""), "Gray", IF(AND([% Complete]@row < 0.5, [Projected End Date]@row < TODAY(30)), "Red", IF(AND([% Complete]@row < 0.75, [Projected End Date]@row < TODAY(30)), "Yellow", IF(OR([% Complete]@row > 0.75, [Projected End Date]@row >= TODAY(30)), "Green")))), IF(OR(Status@row = "On Hold", Status@row = "Cancelled"), "Gray", IF(AND([Projected End Date]@row = "", Status@row = ""), "", IF(OR(Status@row = "Complete", [Projected End Date]@row > TODAY(7)), "Green", IF((TODAY() - [Projected End Date]@row > 0), "Red", IF(OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = ""), "Yellow", ""))))))



    I'll break it down for you:

    =IF(COUNT(ANCESTORS(Task@row)) = 0,

    If the current row has NO Parent rows (so this only applies to your Top Row), then do the following:

    IF(AND([Start Date]@row = "", [Projected End Date]@row = ""), "Gray", IF(AND([% Complete]@row < 0.5, [Projected End Date]@row < TODAY(30)), "Red", IF(AND([% Complete]@row < 0.75, [Projected End Date]@row < TODAY(30)), "Yellow", IF(OR([% Complete]@row > 0.75, [Projected End Date]@row >= TODAY(30)), "Green")))),

    Otherwise, do the following:

    IF(OR(Status@row = "On Hold", Status@row = "Cancelled"), "Gray", IF(AND([Projected End Date]@row = "", Status@row = ""), "", IF(OR(Status@row = "Complete", [Projected End Date]@row > TODAY(7)), "Green", IF((TODAY() - [Projected End Date]@row > 0), "Red", IF(OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = ""), "Yellow", ""))))))



    These are the statements in the formula above:

    PARENT ROW FORMULA

    • If the Parent Start Date is empty and the End Date is empty, return blank
    • If the % Complete is less than 50% and the End Date is in the next 30 days, return Red
    • If the % Complete is less than 75% and the End Date is in the next 30 days, return Yellow
    • If the % Complete is greater than 75% OR the End Date is more than 30 days in the future, return Green

    ALL OTHER ROWS

    • If the Status is either "On Hold" or "Cancelled", return Gray
    • If the End Date and the Status are both blank, then return blank
    • If either the Status is "Complete" or the End Date is greater than 7 days from today, return Green
    • If the End Date is in the Past, return Red
    • If either the Status is "Not Started", "In Progress" or is blank (but has an End Date), return "Yellow"


    Let me know if this fits all of your criteria or not!

    Cheers,

    Genevieve

  • Thank you! This is working, I am just noticing a few one-offs that are appearing blank and I'm not sure why?

    Projected end date and data both have data.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @ecarrero

    What is that Status for that row?

    There's only one statement that takes into account End Dates that are less than 7 days in the future (and not in the past), but the status needs to be "Not Started", "In Progress", or blank to be Yellow.

  • They are both in the "at risk" status.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @ecarrero

    What colour would you like that status to show? Red or Yellow?

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Ok great! Add this to your formula then:

    IF(Status@row = "at risk", "Red"


    I would suggest putting it at the beginning of your child row statements so that this will be the first criteria looked at:

    =IF(COUNT(ANCESTORS(Task@row)) = 0, IF(AND([Start Date]@row = "", [Projected End Date]@row = ""), "Gray", IF(AND([% Complete]@row < 0.5, [Projected End Date]@row < TODAY(30)), "Red", IF(AND([% Complete]@row < 0.75, [Projected End Date]@row < TODAY(30)), "Yellow", IF(OR([% Complete]@row > 0.75, [Projected End Date]@row >= TODAY(30)), "Green")))), IF(Status@row = "at risk", "Red", IF(OR(Status@row = "On Hold", Status@row = "Cancelled"), "Gray", IF(AND([Projected End Date]@row = "", Status@row = ""), "", IF(OR(Status@row = "Complete", [Projected End Date]@row > TODAY(7)), "Green", IF((TODAY() - [Projected End Date]@row > 0), "Red", IF(OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = ""), "Yellow", "")))))))

    Cheers,

    Genevieve

  • THANK YOU SO MUCH!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!