FORMULA - Trying to set a Status based on Date and %Complete

mheaps38436

I'm trying to set the RYG status of a task based on End Date and % Complete of the task.  I've put a formula in place using =IF(AND(...  but I keep getting the error "# INCOMPLETE ARGUMENT SET"

Below is the formula, can anyone tell me what I'm doing wrong or missing, please....

 

=IF(AND([End Date]19 > TODAY(+10), [% Complete]19 < "100%", "Green", IF(AND([End Date]19 < TODAY(+5), [% Complete]19 < "100%", "Yellow", IF(AND([End Date]19 <= TODAY(), [% Complete]19 < "100%", "Red"))))))

Mike Wilday

There could actually be a couple issues at work. One, you have to close the And statements before moving on with the then results. And two, It might be that smartsheet percentages are actually decimals in the back end. 

Try this first:

=IF(AND([End Date]19 > TODAY(+10), [% Complete]19 < "100%"), "Green", IF(AND([End Date]19 < TODAY(+5), [% Complete]19 < "100%"), "Yellow", IF(AND([End Date]19 <= TODAY(), [% Complete]19 < "100%"), "Red")))

If that doesn't work accurately, try this. 

=IF(AND([End Date]19 > TODAY(+10), [% Complete]19 < 1), "Green", IF(AND([End Date]19 < TODAY(+5), [% Complete]19 < 1), "Yellow", IF(AND([End Date]19 <= TODAY(), [% Complete]19 < 1), "Red")))