Help with Formula

NathanF

I have the following formula that returns as incorrect syntax and I can't seem to resolve it:

=IF(AND([Actual Completion Date]@row "", IF([Estimated Completion Date]@row = "", "", IF([Estimated Completion Date]@row <= TODAY(+5), "Red", IF([Estimated Completion Date]@row <= TODAY(+14), "Yellow", IF([Estimated Completion Date]@row <= TODAY(+21), "Green", "Blue"))))))

The formula (or one better formatted by someone with more experience) should check Column "Actual Completion Date"@row to see if it is Not Blank

If the row is Blank then check Column "Estimated Completion Date"@row and change a Status Icon color based on closeness to due date.

If "Actual Completion Date" is Not Blank the formula should do nothing further.

I can confirm the formula works if I remove the AND statement.

Ross Loomis

right off the top of my head you are missing an = for your first logical_expression of the AND function, then a Closed Parentheses to close the and function

=IF(AND([Actual Completion Date]@row = "", IF([Estimated Completion Date]@row = ""), "", IF([Estimated Completion Date]@row <= TODAY(+5), "Red", IF([Estimated Completion Date]@row <= TODAY(+14), "Yellow", IF([Estimated Completion Date]@row <= TODAY(+21), "Green", "Blue")))

Ross Loomis

right off the top of my head you are missing an = for your first logical_expression of the AND function, then a Closed Parentheses to close the and function

=IF(AND([Actual Completion Date]@row = "", IF([Estimated Completion Date]@row = ""), "", IF([Estimated Completion Date]@row <= TODAY(+5), "Red", IF([Estimated Completion Date]@row <= TODAY(+14), "Yellow", IF([Estimated Completion Date]@row <= TODAY(+21), "Green", "Blue")))

NathanF

@Ross Loomis Thanks for that. The formula now works correctly.