Seeking column schedule health formula

ABPM1

I'm looking for a single formula to put into a column that looks at task level performance (assigns RAG based on status - not started, in progress, complete - vs. end date) and also incorporates parent level formula to assign appropriate RAG at the parent level based on status of child tasks - may have multiple parent levels. Thank you!

David Jasven

Try this... I normally use RYG ..

=IF(

  ISPARENT(),

  IF(

    COUNT(CHILDREN([Status]@row)) = COUNTIF(CHILDREN([Status]@row), "Complete"),

    "Green",

    IF(

      OR(

        COUNTIF(CHILDREN([Status]@row), "In Progress") > 0,

        COUNTIF(CHILDREN([End Date]@row), TODAY()) > 0

      ),

      "Amber",

      "Red"

    )

  ),

  IF(

    [Status]@row = "Complete",

    "Green",

    IF(

      OR(

        [Status]@row = "In Progress",

        [End Date]@row <= TODAY()

      ),

      "Amber",

      "Red"

    )

  )

)

Here's a breakdown of the logic:For Parent Rows: The formula checks if all child tasks are marked as "Complete". If true, it assigns "Green". If any child tasks are "In Progress" or their end date is today or has passed (indicating they're overdue), it assigns "Amber". Otherwise, it defaults to "Red".For Child Rows: It directly checks the task's status. If "Complete", it's "Green". If the task is "In Progress" or overdue (end date is today or before), it's "Amber". All other conditions default to "Red".Remember to replace [Status] and [End Date] with the actual column names in your sheet. This formula assumes your Status column can contain "Not Started", "In Progress", and "Complete", and your End Date column contains the due dates for tasks. Adjust the formula as necessary to fit your specific column names and criteria for RAG status determination.

ABPM1

Thank you - I'll give this a try!