Assistance Needed with PMO Formula.

Saj

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,

Bassam Khalil

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.

Bassam Khalil

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.

Saj

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

Bassam Khalil

You Are welcome @Saj