Assistance Needed with PMO Formula.

Options
Saj
Saj ✭✭✭
edited 05/14/24 in Formulas and Functions

Looking for some help, with the following criteria that I haven't been able to find in the community.

Task health formula:
[ Green ] If task Not Started, In Progress, Completed, Cancelled and End-Date is today+future.
[ Red ] If task End-Date is in the past and task is Not-Started or In-Progress.

Grand-Parent row:
[ Red ] if 3 or more (children only) tasks are Red otherwise [ Green ]. Please note there are 4 parents (Planning, Execution, Monitoring and Closing).

Thanks in advance,

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Saj

    please try the following:

    1: Create Helper Columns
    1. Task Status (Dropdown): This column should have values like "Not Started," "In Progress," "Completed," and "Cancelled."
    2. End Date (Date): This column will hold the end date of each task.
    3. Task Health (Text/Number): This column will display the health status of each task (Green or Red).
    4. Child Task Count (Text/Number): This column will be used to count the number of red child tasks for each grand-parent row.
    2: Set Up Task Health Formula
    In the Task Health column, use the following formula to determine the task health based on the criteria:
    For Green Tasks:
    =IF(OR([End Date]@row >= TODAY(), [Status]@row = "Completed", [Status]@row = "Cancelled"), "Green", "")
    

    For Red Tasks:

    =IF(AND([End Date]@row < TODAY(), OR([Status]@row = "Not Started", [Status]@row = "In Progress")),
     "Red", [Task Health]@row)
    

    Combine both conditions:

    =IF(OR([End Date]@row >= TODAY(), [Status]@row = "Completed", [Status]@row = "Cancelled"),
     "Green", IF(AND([End Date]@row < TODAY(), OR([Status]@row = "Not Started",
      [Status]@row = "In Progress")), "Red", ""))
    
    To calculate the number of red child tasks for each grand-parent row, use the Child Task Count column.
    Create a Parent Indicator (Checkbox): This column indicates if a row is a grand-parent row (Planning, Execution, Monitoring, Closing).
    Create a Parent Task Column (Text/Number): This column contains the grand-parent row name for each child task.
    Formula to Count Red Child Tasks:
    In the Child Task Count column for the grand-parent rows, use the following formula:
    =COUNTIF(CHILDREN([Task Health]@row), "Red")
    

    Red if 3 or More Red Child Tasks:
    Apply conditional formatting to the Grand-Parent Row using the condition:

    =IF([Child Task Count]@row >= 3, "Red", "Green")
    

    Green if Less than 3 Red Child Tasks:
    The else condition can be set as green.

    Example

    Task Name

    Status

    End Date

    Task Health

    Child Task Count

    Parent Indicator

    Parent Task

    Planning

    Task 1

    In Progress

    2024-05-10

    Planning

    Task 2

    Not Started

    2024-05-12

    Planning

    Task 3

    Completed

    2024-05-15

    Planning

    Execution

    Task 4

    Not Started

    2024-05-08

    Execution

    Task 5

    In Progress

    2024-05-09

    Execution

    Monitoring

    Task 6

    In Progress

    2024-05-11

    Monitoring

    Closing

    Task 7

    Completed

    2024-05-13

    Closing

    1. Ensure all column references in formulas match the actual column names in your Smartsheet.
    2. After setting up the formulas, apply the necessary conditional formatting rules to the Task Health and Child Task Count columns to visually represent the health statuses.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Saj

    please try the following:

    1: Create Helper Columns
    1. Task Status (Dropdown): This column should have values like "Not Started," "In Progress," "Completed," and "Cancelled."
    2. End Date (Date): This column will hold the end date of each task.
    3. Task Health (Text/Number): This column will display the health status of each task (Green or Red).
    4. Child Task Count (Text/Number): This column will be used to count the number of red child tasks for each grand-parent row.
    2: Set Up Task Health Formula
    In the Task Health column, use the following formula to determine the task health based on the criteria:
    For Green Tasks:
    =IF(OR([End Date]@row >= TODAY(), [Status]@row = "Completed", [Status]@row = "Cancelled"), "Green", "")
    

    For Red Tasks:

    =IF(AND([End Date]@row < TODAY(), OR([Status]@row = "Not Started", [Status]@row = "In Progress")),
     "Red", [Task Health]@row)
    

    Combine both conditions:

    =IF(OR([End Date]@row >= TODAY(), [Status]@row = "Completed", [Status]@row = "Cancelled"),
     "Green", IF(AND([End Date]@row < TODAY(), OR([Status]@row = "Not Started",
      [Status]@row = "In Progress")), "Red", ""))
    
    To calculate the number of red child tasks for each grand-parent row, use the Child Task Count column.
    Create a Parent Indicator (Checkbox): This column indicates if a row is a grand-parent row (Planning, Execution, Monitoring, Closing).
    Create a Parent Task Column (Text/Number): This column contains the grand-parent row name for each child task.
    Formula to Count Red Child Tasks:
    In the Child Task Count column for the grand-parent rows, use the following formula:
    =COUNTIF(CHILDREN([Task Health]@row), "Red")
    

    Red if 3 or More Red Child Tasks:
    Apply conditional formatting to the Grand-Parent Row using the condition:

    =IF([Child Task Count]@row >= 3, "Red", "Green")
    

    Green if Less than 3 Red Child Tasks:
    The else condition can be set as green.

    Example

    Task Name

    Status

    End Date

    Task Health

    Child Task Count

    Parent Indicator

    Parent Task

    Planning

    Task 1

    In Progress

    2024-05-10

    Planning

    Task 2

    Not Started

    2024-05-12

    Planning

    Task 3

    Completed

    2024-05-15

    Planning

    Execution

    Task 4

    Not Started

    2024-05-08

    Execution

    Task 5

    In Progress

    2024-05-09

    Execution

    Monitoring

    Task 6

    In Progress

    2024-05-11

    Monitoring

    Closing

    Task 7

    Completed

    2024-05-13

    Closing

    1. Ensure all column references in formulas match the actual column names in your Smartsheet.
    2. After setting up the formulas, apply the necessary conditional formatting rules to the Task Health and Child Task Count columns to visually represent the health statuses.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Saj
    Saj ✭✭✭
    Options

    @Bassam Khalil , Thank you for the detailed reply and formulas. All of those are working just had to change column names.😊

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    You Are welcome @Saj

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!