Heath Status Symbol - Parent Row Setup

Samuel15

Hey Guys,

Brand new user here trying to understand the correct formula for a simple project health status.

Have new project where all children roll up to one parent row with start date, end date & % complete.

Would like to have a simple Red, Yellow & Green symbol based upon the parent row that shows overall project status based upon the parent Start , End Date & % Complete.

Can anyone help me in understanding how to set this up?

Hope this makes sense.

Thank you in advance !

Samual

Paul Newcome

Try this:

=IF([% Complete]@row = 1, "Blue", IF([Due Date]@row< TODAY(), "Red", IF([Due Date]@row<= TODAY(10), "Yellow", "Green")))

Paul Newcome

So you want all of the child rows to match the parent row RYG? What would be the logic for populating the parent row RYG?

Samuel15

Hi Paul,

We just want the child rows to roll up the overall % Complete for the parent which they do.

Now I'm just looking for the RYG status for the parent overall % Complete to reflect the status of the project at a high level . We might add to all the children rows at a later time for more detail.

Thanks for the help !

Sam

Paul Newcome

If everything is rolling up then you should be able to apply a column formula so that every row is based on the criteria you are wanting for the RYG.

Samuel15

Hey Paul,

Here's what I'm looking for below, correct me if my logic is backwards. I looked at some examples and modified them to get parse errors.

·       COMPLETE - Blue: % Complete is 100% regardless of the date.

·       PAST DUE - Red: Today’s Date is 1 day after Due Date AND % Complete is < 100%

·       Behind - Yellow: Today’s Date is 10 before Due Date AND % Complete is < 75%

·       On Track - Green: Until its hits the Behind Yellow above.

Thanks for your help.

Sam

Paul Newcome

Try this:

=IF([% Complete]@row = 1, "Blue", IF([Due Date]@row< TODAY(), "Red", IF([Due Date]@row<= TODAY(10), "Yellow", "Green")))

Samuel15

That worked !

Thank You

Sam

Samuel15

Hey Paul,

The formula worked, how can we modify it so that when the % complete is below 75% within 10 days shows behind, other wise anything above 75% shows on track regardless of the 10 day count?

Paul Newcome

So you want what exactly? Yellow only if within 10 days and less than 75%. If it is 76% and due tomorrow, it still shows green?

Samuel15

Hey Paul,

Not thinking through the process enough, the formula you provided will get us there.

Thank you again for ur help!

Sam

Samuel15

Hi Paul,

Could you look at the formula below and tell me what I missed, getting error.

Trying to get : Complete , Not Started , In Progress & Past Due

=IF([% Complete]@row >= 1, "Complete", IF(OR([% Complete]@row = "", [% Complete]@row

= 0), "Not Started", "In Progress", IF[TODAY() > [Due Date]@row, "Past Due")))

Thank You

Sam

Paul Newcome

Try this:

=IF([% Complete]@row = 1, "Complete", IF([Due Date]@row< TODAY(), "Past Due", IF([% Complete]@row> 0, "In Progress", "Not Started")))