Help with an IF statement

Bill Brandt

I am trying to get the R/Y/G/B status to change based on some other cell values and am using this

 

=IF(([End Date]17 - TODAY()) < 1, "Red", IF(([End Date]17 - TODAY()) < 5, "Yellow", IF((ISBOOLEAN(Complete17)), "Blue", "Green")))

 

The first part works fine. I wan the icon to go blue if the "Completed" checkbox is checked but can't seem to get it to work after many variations of the above. probably a simple sytax error that I am not seeing.

 

thanks

J. Craig Williams

Bill,

 

Try this:

 

=IF(Completed17, "Blue", IF(([End Date]17 - TODAY()) < 1, "Red", IF(([End Date]17 - TODAY()) < 5, "Yellow", "Green")))

 

I believe you want Blue if the item is complete, regardless of date. So I moved Blue to the first IF check.

IF statements work until they find a condition and then stop. 

If the date is in the past (red) or within 5 days (yellow), then the formula does not care about the Complete column value in your formula.

 

You also called the Completed column a check box column. If so, you don't need ISBOOLEAN. That checks if the value is a BOOLEAN (which it should be, since it is a check box.  Rather, you want to see the value Completed17.

 

Lastly, you called the check box column "Completed" in your text but "Complete" in your formula. I assume that was a typo and the column is Completed, so that is what my formula uses. You can copy and paste out of the formula celll instead of retypiing next time.

 

Hope this helps.

 

Craig

 

Bill Brandt

Thanks,

 When IF statements are flat like that I tend to forget the order of execution.

 

Bill

J. Craig Williams

If there are more than 2 nested if's, I tend to write psuedo code to follow them later.

 

if (this) then

  do this

else if (this other thing) then

  do this

else if (this other thing #2) then

  do this

etc...

 

Helps a bit on the first time and loads when I have to come back and fix/change it.

Craig