Formula for enabling heath column colored dots

Tawoolie

Hi All,

I am trying to enable the colored dots in the health column based on planned start/finish and actual start/finish. I have tried many different formulas and none has worked so far.

Here is what I am trying to achieve,

If "Status" = On Hold or Cancelled -> Enable blue dot

If "Start Date">"Actual Start" -> Enable yellow dot

If "End Date"<"Actual Finish" -> Enable red dot

If "End Date">="Actual Finish" -> Enable green dot

Any guidance would be much appreciated.

ShelbyWarren

@Tawoolie Try this:

=IF(OR(Status@row = "On Hold", Status@row = "Canceled"), "Blue", IF([Start Date]@row > [Actual Start]@row, "Yellow", IF([End Date]@row < [Actual Finish]@row, "Red", IF([End Date]@row >= [Actual Finish]@row, "Green", ""))))

Paul McGuinness

Hi @Tawoolie

This formula should do what you need

=IF(OR(Status@row = "On Hold", Status@row = "Cancelled"), "Blue", IF([Start date]@row > [Actual start]@row, "Yellow", IF([End date]@row < [Actual Finish]@row, "Red", IF([End date]@row >= [Actual Finish]@row, "Green", "Error"))))

Tested below and working

However you may need to reprioritise the IF statements in the formula if you are not getting the desired health colour where Yellow is appearing instead of red etc. because the yellow statement is ahead of the red in the formula.

Hope that helps

Thanks

Paul

KevinPlut

Hi, I made a quick example and hope it covers most of your actual data possibilities. See screenshot and formula below. I hope this helps. For my status column, I made a drop down with "active," "on hold," and "cancelled."

=IF(Status@row <> "Active", "Blue", IF([Start Date]@row > [Actual Start]@row, "Yellow", IF([Finish Date]@row >= [Actual Finish]@row, "Green", IF([Finish Date]@row < [Actual Finish]@row, "Red"))))

ShelbyWarren

@Tawoolie Try this:

=IF(OR(Status@row = "On Hold", Status@row = "Canceled"), "Blue", IF([Start Date]@row > [Actual Start]@row, "Yellow", IF([End Date]@row < [Actual Finish]@row, "Red", IF([End Date]@row >= [Actual Finish]@row, "Green", ""))))

Paul McGuinness

Hi @Tawoolie

This formula should do what you need

=IF(OR(Status@row = "On Hold", Status@row = "Cancelled"), "Blue", IF([Start date]@row > [Actual start]@row, "Yellow", IF([End date]@row < [Actual Finish]@row, "Red", IF([End date]@row >= [Actual Finish]@row, "Green", "Error"))))

Tested below and working

However you may need to reprioritise the IF statements in the formula if you are not getting the desired health colour where Yellow is appearing instead of red etc. because the yellow statement is ahead of the red in the formula.

Hope that helps

Thanks

Paul

KevinPlut

Hi, I made a quick example and hope it covers most of your actual data possibilities. See screenshot and formula below. I hope this helps. For my status column, I made a drop down with "active," "on hold," and "cancelled."

=IF(Status@row <> "Active", "Blue", IF([Start Date]@row > [Actual Start]@row, "Yellow", IF([Finish Date]@row >= [Actual Finish]@row, "Green", IF([Finish Date]@row < [Actual Finish]@row, "Red"))))

Tawoolie

Hi @ShelbyWarren, it worked. I realized that I was using this formula with punctuation errors and the result was coming as "unparsable". Thank you very much for your help fixing it for me.

Thanks @Paul McGuinness and @KevinPlut for the demo.