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!
