Stoplight for Project plan tasks
Greetings! New to SmartSheet formulas  so need help on some of the basics, I guess :)...
Logic / Pseudo required:
Formula below tested but could not be parsed:
=IF([%Complete]@row = 1, "Blue",
IF(AND([%Complete]@row < 1,[BSLN_Finish1]@row < TODAY()),"Red",
IF(AND([%Complete]@row < 1,[BSLN_Finish1]@row > TODAY(),AND ([LBE_Finish]@row > [BSLN_Finish1]@row)), "Yellow",
IF(AND([%Complete]@row < 1,[BSLN_Finish1]@row > TODAY(),AND ([LBE_Finish]@row < TODAY()), "Yellow",
"Green")))))
Any advise??
Best Answer

Hi @bct
You're close!
Since you already state certain requirements (such as % Complete = 1) then we don't need to state the opposite in later requirements.
For example:
=IF([%Complete]@row = 1, "Blue",
IF([BSLN_Finish1]@row < TODAY(), "Red",
^ The "Red" rule doesn't need to state that % is less than 1 since it will only move on to the next IF when the first IF is Not True.
There's also a double "AND" in your third condition that we can remove.
Try something like this:
=IF([%Complete]@row = 1, "Blue",
IF([BSLN_Finish1]@row < TODAY(), "Red",
IF([LBE_Finish]@row > [BSLN_Finish1]@row, "Yellow",
IF([LBE_Finish]@row < TODAY(), "Yellow",
"Green"))))
Let me know if this makes sense and works for your purposes! I will note that blank cells are seen as "in the past" in date columns, so you may see Yellow if your Finish date is blank. This is where we may want to add in an AND statement to only check cells that contain a date.
Cheers,
Genevieve
Answers

Hi @bct
You're close!
Since you already state certain requirements (such as % Complete = 1) then we don't need to state the opposite in later requirements.
For example:
=IF([%Complete]@row = 1, "Blue",
IF([BSLN_Finish1]@row < TODAY(), "Red",
^ The "Red" rule doesn't need to state that % is less than 1 since it will only move on to the next IF when the first IF is Not True.
There's also a double "AND" in your third condition that we can remove.
Try something like this:
=IF([%Complete]@row = 1, "Blue",
IF([BSLN_Finish1]@row < TODAY(), "Red",
IF([LBE_Finish]@row > [BSLN_Finish1]@row, "Yellow",
IF([LBE_Finish]@row < TODAY(), "Yellow",
"Green"))))
Let me know if this makes sense and works for your purposes! I will note that blank cells are seen as "in the past" in date columns, so you may see Yellow if your Finish date is blank. This is where we may want to add in an AND statement to only check cells that contain a date.
Cheers,
Genevieve

Thank you, Genevieve! I think, I was able to use the code and worked great!
Also, you have a good point about flagging the missing LBE date. i thought for the time being, I would keep a "No Date" as Yellow by design, so that it flags the missing LBE Finish value in the cell...
Also, not finding any limitation in the tool, and infact, it was very easy to set the 4 colours for a layman like me. However, out of curiosity, is it possible to add more stop light indicators? Not trying to make it too complex, but for example adding a different indicator (FLAG etc)  for any milestone which is already missed and past due?

Hi @bct
I'm glad this worked for you! We can add in the rule about the blank date being Yellow before your Red rule. I also combined your two latter Yellow rules into one, using an OR function:
=IF([%Complete]@row = 1, "Blue",
IF([LBE_Finish]@row = "", "Yellow",
IF([BSLN_Finish1]@row < TODAY(), "Red",
IF(OR([LBE_Finish]@row > [BSLN_Finish1]@row, [LBE_Finish]@row < TODAY()), "Yellow",
"Green"))))
In regards to adding a Flag, you could definitely do that, but it would need to be in a separate column (e.g. one column with the status ball colours, one column to flag rows). See Available Symbols in the Symbols Column
An alternative would be to use Conditional Formatting, so when your formula turns the ball above to be "red" you could highlight that row automatically, see: Apply formatting automatically with conditional formatting rules
Cheers,
Genevieve
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 62 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!