Seeking column schedule health formula

Options
ABPM1
ABPM1 ✭✭✭
edited 03/27/24 in Formulas and Functions

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!

Answers

  • David Jasven
    David Jasven ✭✭✭✭
    Options

    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
    ABPM1 ✭✭✭
    Options

    Thank you - I'll give this a try!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!