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

• Employee

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

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

«1

• ✭✭✭✭✭✭

Are you getting an error or an unexpected output?

• ✭✭

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

• ✭✭✭✭✭✭

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?

• ✭✭

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.

• Employee

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

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• ✭✭

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.

• Employee

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.

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• ✭✭

They are both in the "at risk" status.

• Employee

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

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• ✭✭
• Employee

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

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• ✭✭

THANK YOU SO MUCH!

• ✭✭✭✭

@Genevieve P. hello! I love this formula and started using it. However, I have a question - how would I add language to show a red status if the project end date passes and is not complete?

• Employee

Hey @Tony Fronza

It's great to hear a formula from a year ago is helping you today! 🙂

You can add an OR statement to the first IF where it says to generate Red.

Here's the original:

IF(AND([% Complete]@row < 0.5, [Projected End Date]@row < TODAY(30)), "Red",

Updated:

IF(OR(AND([% Complete]@row < 0.5, [Projected End Date]@row < TODAY(30)), AND(Status@row <> "Complete", [Projected End Date]@row <= TODAY())), "Red",

For the child rows, this is already outlined. I've bolded the two sections below:

=IF(COUNT(ANCESTORS(Task@row)) = 0, IF(AND([Start Date]@row = "", [Projected End Date]@row = ""), "Gray", IF(OR(AND([% Complete]@row < 0.5, [Projected End Date]@row < TODAY(30)), AND(Status@row <> "Complete", [Projected End Date]@row <= TODAY())), "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

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭

Thanks, @Genevieve P.! I tried copying and pasting the updated formula above but received a formula syntax error.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!