Formula for enabling heath column colored dots

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.

Best Answers

  • ShelbyWarren
    ShelbyWarren ✭✭✭✭✭
    Answer ✓

    @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
    Paul McGuinness Overachievers
    Answer ✓

    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
    KevinPlut ✭✭
    Answer ✓

    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"))))


Answers

  • ShelbyWarren
    ShelbyWarren ✭✭✭✭✭
    Answer ✓

    @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
    Paul McGuinness Overachievers
    Answer ✓

    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
    KevinPlut ✭✭
    Answer ✓

    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"))))


  • 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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!