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
-
@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", ""))))
-
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
-
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
-
@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", ""))))
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!