Status color with IF statements

aecross

Trying to change the status colors based on criteria being met in an if/then statement.

Here are the 3 things i need to account for:

1. If projected construction complete is in the past AND actual construction complete is blank, the status bubble should be Red.
2. If projected construction complete is in the next 15 days AND actual construction complete is blank, the status bubble should be Yellow.
3. if actual construction start is a date AND projected construction start is blank, the status bubble should be Red.

The first If/And function works but I'm having trouble adding on to it. I am receiving the #UNPARSABLE error.

=IF(AND([PROJECTED CONSTRUCTION COMPLETE]@row <= TODAY(), ISBLANK([ACTUAL CONSTRUCTION COMPLETE]@row)), "Red"), IF(AND([PROJECTED CONSTRUCTION COMPLETE]@row <= TODAY(-15), ISBLANK([ACTUAL CONSTRUCTION COMPLETE]@row)), "Yellow"), IF(AND(ISDATE([ACTUAL CONSTRUCTION START]@row, ISBLANK([PROJECTED CONSTRUCTION COMPLETE]@row, "Red", "Green")))

Nik Fuentes

the ")" you have after the "Red" closes the first IF() statement, so the following one isn't being read as an else clause. You have the same issue with your second IF() statement, the "Yellow" one and your 3rd is all wrong.

Attached you should find a breakdown of what you have. I've spaced it out like you would see in a coding view so it should be easier to follow where your functions open and close. Below that is my corrected version. for the record, all I needed to do was adjust where you closed certain formulae. Good job otherwise!

Status Toggle SSC.txt