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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!