Complex nested IF, AND with traffic light symbols

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:

If the Finish date is in the past, set the symbol to Green (assume activity is complete)
If TODAY() is between start and finish dates, set the symbol to Yellow (assume the activity is in progress)
If the Start date is in the future, set the symbol to Gray (assume the activity is not started yet)
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

Find more posts tagged with

Formulas

Reports

Support

Solution Center

Conditional Formatting

Gantt View

Project Management

Email Notifications

Comments

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.

IF there is a hard date in Date22 AND it is less than Finish22, THEN set the Symbol to Red.
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:

If the due date is in the past and % complete is less than 100 then health is Red
and if the due date is in the future and % complete is less than 100 then health is grey
and if the due date is in the past and % complete is equal to 100, then health is green
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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…