Linking Green Health Indicator with Not Started Status

Marcia562

Used this ask a question feature for the first time last week and it has been a lifesaver. Here goes another one... How do I update my formula's to show the following. Not sure if I need to update both the health and status, or just the health.

1. Show status as 'in progress' if the start date is greater than today's date because it's currently showing 'in progress' for tasks that haven't started yet, image below for reference (formula says: if the task has not yet started, but it starts within the next 30 workdays, return "green" to indicate that the task is coming up)
   1. Should I tie the 'in progress' to the % complete field? For example, show 'in progress' if % complete is greater than 1% or am I overthinking this?
2. Show green in 'schedule health' if the task has not started yet because it's currently showing up blank, image below for reference (formula says: if task has not started yet but starts within the next 30 workdays, show "green"; otherwise, show nothing)

Health

=IF([Schedule Health]@row = "Blue", "Complete", IF([Schedule Health]@row = "Green", "In Progress", IF([Schedule Health]@row = "Yellow", "At Risk", IF([Schedule Health]@row = "Red", "Late", IF([Schedule Health]@row = "", "Not Started", "")))))

Status

=IF([% Complete]@row = 1, "Blue", IF([End Date]@row < TODAY(), "Red", IF(AND([Start Date]@row <= TODAY(), ABS(NETWORKDAYS(TODAY(), [Start Date]@row) + 1) / ABS(NETWORKDAYS([Start Date]@row, [End Date]@row)) > [% Complete]@row), "Yellow", IF(AND([Start Date]@row <= TODAY(), ABS(NETWORKDAYS(TODAY(), [Start Date]@row) + 1) / ABS(NETWORKDAYS([Start Date]@row, [End Date]@row)) <= [% Complete]@row), "Green", IF(AND([Start Date]@row > TODAY(), [Start Date]@row <= WORKDAY(TODAY(), 30)), "Green", "")))))

Genevieve P.

Hi @Marcia562

It sounds like you have two conflicting rules here - you want the Health to be Green if the row is in the future, but the Status to be "Not Started". Right now you have the Status based on the Green ball, which means that it will always show "In Progress" if there's a Green health, regardless of the date.

This one is an easy fix! We can check the Green health and the Start Date being today or in the past:

=IF([Schedule Health]@row = "Blue", "Complete", IF(AND([Schedule Health]@row = "Green", [Start Date]@row <= TODAY()), "In Progress", IF([Schedule Health]@row = "Yellow", "At Risk", IF([Schedule Health]@row = "Red", "Late", IF([Schedule Health]@row = "", "Not Started", "")))))

^ Notice here at the end though, you're saying that a blank health is Not Started. But the adjustment you want to make is to show a green health for all not started rows. This means we'll want to change that statement as well, to ensure it's focussing on Dates and not Health:

=IF([Schedule Health]@row = "Blue", "Complete", IF(AND([Schedule Health]@row = "Green", [Start Date]@row <= TODAY()), "In Progress", IF([Schedule Health]@row = "Yellow", "At Risk", IF([Schedule Health]@row = "Red", "Late", IF([Start Date]@row > TODAY(), "Not Started", "")))))

Then for your Health, we can simply remove the time constraints around the final "Green" status instruction, so it shows Green regardless of the 30 day rule:

=IF([% Complete]@row = 1, "Blue", IF([End Date]@row < TODAY(), "Red", IF(AND([Start Date]@row <= TODAY(), ABS(NETWORKDAYS(TODAY(), [Start Date]@row) + 1) / ABS(NETWORKDAYS([Start Date]@row, [End Date]@row)) > [% Complete]@row), "Yellow", IF(AND([Start Date]@row <= TODAY(), ABS(NETWORKDAYS(TODAY(), [Start Date]@row) + 1) / ABS(NETWORKDAYS([Start Date]@row, [End Date]@row)) <= [% Complete]@row), "Green", IF([Start Date]@row > TODAY(), "Green", "")))))

Let me know if these work for you!

Cheers,

Genevieve

Genevieve P.

Hi @Marcia562

It sounds like you have two conflicting rules here - you want the Health to be Green if the row is in the future, but the Status to be "Not Started". Right now you have the Status based on the Green ball, which means that it will always show "In Progress" if there's a Green health, regardless of the date.

This one is an easy fix! We can check the Green health and the Start Date being today or in the past:

=IF([Schedule Health]@row = "Blue", "Complete", IF(AND([Schedule Health]@row = "Green", [Start Date]@row <= TODAY()), "In Progress", IF([Schedule Health]@row = "Yellow", "At Risk", IF([Schedule Health]@row = "Red", "Late", IF([Schedule Health]@row = "", "Not Started", "")))))

^ Notice here at the end though, you're saying that a blank health is Not Started. But the adjustment you want to make is to show a green health for all not started rows. This means we'll want to change that statement as well, to ensure it's focussing on Dates and not Health:

=IF([Schedule Health]@row = "Blue", "Complete", IF(AND([Schedule Health]@row = "Green", [Start Date]@row <= TODAY()), "In Progress", IF([Schedule Health]@row = "Yellow", "At Risk", IF([Schedule Health]@row = "Red", "Late", IF([Start Date]@row > TODAY(), "Not Started", "")))))

Then for your Health, we can simply remove the time constraints around the final "Green" status instruction, so it shows Green regardless of the 30 day rule:

=IF([% Complete]@row = 1, "Blue", IF([End Date]@row < TODAY(), "Red", IF(AND([Start Date]@row <= TODAY(), ABS(NETWORKDAYS(TODAY(), [Start Date]@row) + 1) / ABS(NETWORKDAYS([Start Date]@row, [End Date]@row)) > [% Complete]@row), "Yellow", IF(AND([Start Date]@row <= TODAY(), ABS(NETWORKDAYS(TODAY(), [Start Date]@row) + 1) / ABS(NETWORKDAYS([Start Date]@row, [End Date]@row)) <= [% Complete]@row), "Green", IF([Start Date]@row > TODAY(), "Green", "")))))

Let me know if these work for you!

Cheers,

Genevieve

Marcia562

@Genevieve P. that worked, thank you so much 😍