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

aecross

Hi @Nik Fuentes, thank you for this!

I've added in the formula that you broke down, but I'm still getting an unparsable error. I've moved around parenthesis but still can't seem to solve it. Below is my formula.

=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

What's that comma between two ")" at the end there?

the first comma here: ISBLANK([PROJECTED CONSTRUCTION COMPLETE]@row),),

If you delete that, does it work?

aecross

@Nik Fuentes - I included that because your example included it. I've tried it without the comma and i still get the #UNPARSABLE error.

I did try to encompass it all with the IFERROR function but now i am receiving #INCORRECT ARGUMENT SET error.

=IFERROR(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"))))