# What is the issue with my formula?

=IF(AND(% Complete]3 < 1,[End Date]3 < TODAY()), "On Time", IF(AND([% Complete]3 < 1,[End Date]3 = TODAY()), "Due", IF(AND([% Complete]3 < 1,[End Date]3 > TODAY()), "Past Due","Complete")))

If i validate each part of the formula separately it returns my intended value however when I try to embed using IF AND, it is always returning Imparseable.

Tags:

• ✭✭✭✭✭✭

=IF(AND(% Complete]3 < 1,[End Date]3 < TODAY()), "On Time", IF(AND([% Complete]3 < 1,[End Date]3 = TODAY()), "Due", IF(AND([% Complete]3 < 1,[End Date]3 > TODAY()), "Past Due","Complete")))

you are missing a hard bracket on your first %complete. This could also be simplified down to this:

=IF([% Complete]3 < 1,if([End Date]3 < TODAY(), "On Time", IF([End Date]3 = TODAY(), "Due", "Past Due")),"Complete"

• Thank you! Very helpful. That worked!

plus the new formula worked as well. I tried that the first time around but had my closing ) in the wrong place.

• ✭✭✭✭✭✭

yeah, it is kinda reverse logic, I kept it that way because it more closely resembled your original formula, but it makes more logical sense and is easier to write like this:

=IF([% Complete]3 >= 1,"Complete",if([End Date]3 < TODAY(), "On Time", IF([End Date]3 = TODAY(), "Due", "Past Due"

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!