What is the issue with my formula?

Danielle D'Alessandro

=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.

L_123

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

Danielle D'Alessandro

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.

L_123

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"