Project Health Formula

ecarrero

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.

Genevieve P.

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

Paul Newcome

Are you getting an error or an unexpected output?

ecarrero

@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

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?

ecarrero

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

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

ecarrero

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.

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.

ecarrero

They are both in the "at risk" status.

Genevieve P.

Hi @ecarrero

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

ecarrero

Red

Genevieve P.

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

ecarrero

THANK YOU SO MUCH!

Tony Fronza

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

Genevieve P.

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

Tony Fronza

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