Calculated Column with Nested IF statments

Here is my current formula that works.

="<DIV style='font-weight:bold; font-size:24px; color:"&IF([Project Close A]="",IF(Program="CAPITAL",IF([Project Close P]>=[Current Date],"green","red")),IF([Project Close P]>=[Project Close A],"green","red"))&";'>•</DIV>"

I now need to add an additional IF statement that states if Project Cloase P is blank turn yellow. When I add If([Project Close P]="", "yellow" to the front of the above it doesn't work. How do I fix this?

• How about showing what you tried - you've likely made an error like a missing , or ) but without putting what you tried there is no way to know.
– Ryan
May 15, 2012 at 15:58
• Also what field type is Project Close A - is it a date?
– Ryan
May 15, 2012 at 16:00
• Here is what I've tried. ="<DIV style='font-weight:bold; font-size:24px; color:"&IF([Project Close P]="","yellow",IF([Project Close A]="",IF(Program="CAPITAL",IF([Project Close P]>=[Current Date],"green","red")),IF([Project Close P]>=[Project Close A],"green","red"))&";'>•</DIV>") Yes Project Close A are date fields May 15, 2012 at 16:10

Your missing a closing bracket at the end

IF([Project Close P]="","yellow",IF([Project Close A]="",IF(Program="CAPITAL",IF([Project Close P]>=[Current Date],"green","red")),IF([Project Close P]>=[Project Close A],"green","red"))

Should be

IF([Project Close P]="","yellow",IF([Project Close A]="",IF(Program="CAPITAL",IF([Project Close P]>=[Current Date],"green","red")),IF([Project Close P]>=[Project Close A],"green","red")))

Get yourself a decent text editor like Notepad ++ and it will highlight matching pairs of brackets making it really easy to spot things like this.

• I get an error that the formula contains a syntax error or is not supported when I add the ) May 15, 2012 at 17:45
• It works when I remove the script in the front of the formula and at the end. But I need this script to add the colored dots. Any suggestions? May 15, 2012 at 17:52
• I removed an If statement and added the If([Project Status P]="", ""white" and removed a ) in the middle of the nest and addit to the end. So @Ryan I was missing a ), Thanks! Here is the final formula and I attached a picture just in case someone wanted to see. ="<DIV style='font-weight:bold; font-size:24px; color:"&IF([Project Close P]="","white",IF([Project Close A]="",IF([Project Close P]>=[Current Date],"green","red"),IF([Project Close P]>=[Project Close A],"green","red")))&";'>•</DIV>"![enter image description here][1] [1]: i.stack.imgur.com/4dXMS.jpg May 15, 2012 at 19:42