Complex nested IF, AND with traffic light symbols

Johnny Michaelsen

Hi All,

I'm working on automating a symbol highlighter to indicate project progress as well as alerting if key milestones are being compromised. The formula below is structured based on the following columns:

• Finish: The Activity completion date
• Start: The activity start date
• Date: A column containing fixed dates to which certain activities must be completed (these are blank for the most part, but key milestones will be entered into this column).

I'm using the symbols Red/Green/Yellow/Gray, and what I'm trying to achieve is:

1. If the Finish date is in the past, set the symbol to Green (assume activity is complete)
2. If TODAY() is between start and finish dates, set the symbol to Yellow (assume the activity is in progress)
3. If the Start date is in the future, set the symbol to Gray (assume the activity is not started yet)
4. Finally, if the Fixed date is less than the finish date (and not blank), set the symbol to Red (assume the milestone is compromised)

Steps 1-3 above works in the formula below, but I'm struggling to get the final step to work, and as I'm not too familiar with these formulas, I have not been able to get this to work from browsing the support and would appreciate any help to finalize this.

=IF(

  Finish1 < TODAY(), "Green",

  IF(AND(Start1 <= TODAY(), Finish1 >= TODAY()), "Yellow",

  IF(Start1 > TODAY(), "Gray",

  IF(AND(Date1 < Finish1, IF(NOT(ISBLANK(Date1)))), "Red"

)))))

 

Thank you!

 

/Johnny

 

L_123

Original

1 =IF(

2 Finish1 < TODAY(), "Green",

3  IF(AND(Start1 <= TODAY(), Finish1 >= TODAY()), "Yellow",

4  IF(Start1 > TODAY(), "Gray",

5  IF(AND(Date1 < Finish1, IF(NOT(ISBLANK(Date1)))), "Red"

)))))

 

First of all, the second AND statement (Line 3) is useless. You have a stacked if, and the Finish >= Today() part is already checked in the first statement.

1=IF(

2  Finish1 < TODAY(), "Green",

3  IF(Start1 <= TODAY(), "Yellow",

4  IF(Start1 > TODAY(), "Gray",

5  IF(AND(Date1 < Finish1, IF(NOT(ISBLANK(Date1)))), "Red"

 

Next the criteria on line 4 is also redundant. We already filtered by <= today(), so another filter by >today() is redundant.

1=IF(

2  Finish1 < TODAY(), "Green",

3  IF(Start1 <= TODAY(), "Yellow",

4  "Gray"))

X.  IF(AND(Date1 < Finish1, IF(NOT(ISBLANK(Date1)))), "Red"

 

Now X is outside of our stacked if. But there seems to be several issues with the statement so lets take a look at it.

 

The second criteria of the and does not require an if statement, the if is implied. (side note, the if statement wouldn't work anyway as every if statement requires a return before you end it)

 

This leaves us with

IF(AND(Date1 < Finish1, NOT(ISBLANK(Date1))), "Red"

 

So back to our main statement

1=IF(

2  Finish1 < TODAY(), "Green",

3  IF(Start1 <= TODAY(), "Yellow",

4  "Gray"))

X.  IF(AND(Date1 < Finish1, NOT(ISBLANK(Date1))), "Red"

 

The X is still outside our statement, but now it is correct theoretically. We are only using one of the criteria of the if statement, so why don't we move it up a little bit, and use "Gray" as the false of the statement.

1=IF(

2  Finish1 < TODAY(), "Green",

3  IF(Start1 <= TODAY(), "Yellow",

4  IF(AND(Date1 < Finish1, NOT(ISBLANK(Date1))), "Red","Gray"))

or in a format you can copy paste

=IF(Finish1 < TODAY(), "Green",IF(Start1 <= TODAY(), "Yellow", IF(AND(Date1 < Finish1, NOT(ISBLANK(Date1))), "Red","Gray"

Mike Wilday

@L Nice detailed walkthrough. 

 

Johnny Michaelsen

What an amazing walkthrough. Thank you so much!

One problem I just discovered is that the Red does not "override" the yellow, and the Red should be the most important state. Any suggestions on how I might go around ensuring the last statement is the "highest" priority setting?

Thanks again for all your help!

/Johnny

Johnny Michaelsen

A few attempts with something like the following does not seem to parse, so not sure how to change the priorities of status:

 

=IF(AND(Date22 < Finish22, NOT(ISBLANK(Date22))), "Red", IF(Start22 <= TODAY(), "Yellow", IF(Finish22 < TODAY(), "Green",  "Gray”)))

L_123

That is the correct idea, and I'm not sure what is wrong with yours, here is mine.

=IF(AND(Date1 < Finish1, NOT(ISBLANK(Date1))), "Red",IF(Finish1 < TODAY(), "Green", IF(Start1 <= TODAY(), "Yellow", "Gray")))

 

The concept when changing the priority of the stacked if statement is how early they come in the statement. so right now the priority of my statement is:

1. Red 2. Green 3. Yellow 4. Grey

Johnny's priority is

1. Red 2. Yellow 3. Green 4. Grey

 

Which is probably more likely what you would want. The easiest way to change the priority of a stacked if statement, especially a more straightforward one like this, is to post it into notepad and put each if statement on its own line. I've posted some pictures below of how I solved this problem.

L_123

Thanks. I feel like it is much easier when it's broken down into parts, for my own understanding anyway.  This is how I normally problem solve these type of issues, just wrote out my process.

Johnny Michaelsen

So, after a bit more research, it looks like I'd need to separate the highest prio ("Red" symbol) into an OR statement as this should overrule all the other ones regardless of their validations. I'm still not there, but here's where I'm at.

1. IF there is a hard date in Date22 AND it is less than Finish22, THEN set the Symbol to Red.
2. IF none of the above are true, THEN follow the basic rules for Yellow, Green and Gray.

Any suggestions of why the below does not parse, and also guidance on if this would be the correct approach?

 

=IF(OR(AND(Date22 < Finish22, NOT(ISBLANK(Date22))), "Red"), IF(OR(Start22 <= TODAY(), "Yellow", IF(Finish22 < TODAY(), "Green",  "Gray”))))

 

Thanks again for any help cracking this!

/Johnny

Johnny Michaelsen

YASS! You cracked the code

This works a charm, thank you so much for your support, I'm super stoked to have this working and it will help me run things much easier. 

Thanks again!

 

/Johnny

Ola123

Please I need help with this problem.

How do I use the traffic light symbols to indicate:

1. If the due date is in the past and % complete is less than 100 then health is Red
2. and if the due date is in the future and % complete is less than 100 then health is grey
3. and if the due date is in the past and % complete is equal to 100, then health is green
4. and if the due date is in the future and % complete is greater than 50% then health is yellow

Many thanks for your help with this.