Stoplight for Project plan tasks

bct

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??

Genevieve P.

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

Genevieve P.

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

bct

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?

Genevieve P.

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