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

mheaps38436
mheaps38436 ✭
edited 12/09/19 in Formulas and Functions

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"))))))

Tags:
· Share on FacebookShare on Twitter

Comments

  • Mike Wilday
    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")))

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!