Project Health Formula for Using Symbols

Options
edited 08/01/24

I'm having a lot of trouble with creating a formula that shows project and task health with RYG symbols based on the following criteria:

My columns are Status, Start Date, End Date and Date Completed

Green:

If Status is "Not Started" and Start Date is today or only 3 days past.

If Status is "In Progress" and End Date is today or no more than 3 days past.

If Status is "Complete" and Date Completed is less than or equal to 3 days past End Date

Yellow: if Status is "Not Started" and Start Date is more than 3 days past

If Status is "In Progress" and End Date is more than 3 days past.

If Status is "Complete" and Date Completed is more than 3 days past End Date.

Red: if Status is "Not Started" and Start Date is more than 7 days past.

If Status is "In Progress" and End Date is more than 7 days past.

If Status is "Complete" and Date Completed is more than 3 days past End Date.

If the Status is On Hold, or Start Date is Blank then it should be Gray.

Parent rows will also need to average of non-Gray children.

I can get the Parent rows to work with this formula:

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIF(CHILDREN(), "Gray") = COUNT(CHILDREN()), "Gray", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 0.5, "Red", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 1.5, "Yellow", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 2.5, "Green", "Gray"))))

But whenever I try to add any of the other criteria for the children I will get #Unparseable or #Incorrect Argument Set errors. I feel like I may have been too ambitious, but if I can get this column to work it will be much easier to show the health of my projects and with my team members.

Any help is much appreciated!

• ✭✭✭✭✭
Options

@Jessica Lake Days , no you weren't too ambitious. If you can think it logically it can (almost) always be formulated.

1.) Add a column to your sheet (hidden helper) and the name is "C". This stand for children. In that column put a column formula " =count(children()) ". This will simply count the amount of children at every row. We'll use this in the next formula which is the one your looking for.

Use this formula for your colors/RYGG column.

=IF(C@row > 0, IF(COUNT(CHILDREN()) > 0, IF(COUNTIF(CHILDREN(), "Gray") = COUNT(CHILDREN()), "Gray", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 0.5, "Red", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 1.5, "Yellow", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 2.5, "Green", "Gray"))))), IF(Status@row = "On Hold", "Gray", IF(OR(AND(Status@row = "Not Started", ([Start Date]@row - TODAY()) > -3, [Start Date]@row <= TODAY()), AND(Status@row = "In Progress", [End Date]@row >= TODAY(), ([End Date]@row - TODAY()) <= 3), AND(Status@row = "Complete", [Date Completed]@row <= ([End Date]@row + 3))), "Green", IF(OR(AND(Status@row = "Not Started", (TODAY() - [Start Date]@row) > 3), AND(Status@row = "In Progress", (TODAY() - [End Date]@row) > 3), AND(Status@row = "Complete", ([Date Completed]@row - [End Date]@row) > 3)), "Yellow", IF(OR(AND(Status@row = "Not Started", (TODAY() - [Start Date]@row) > 7), AND(Status@row = "In Progress", (TODAY() - [End Date]@row) > 7), AND(Status@row = "Complete", ([Date Completed]@row - [End Date]@row) > 7)), "Red", "")))))

This is roughly 1240 characters so you're well within the 4000 character limit.

Certified Platinum Partner

2023 Partner of the Year

PrimeConsulting.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!