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.

Find more posts tagged with

Formulas

functionality

Accepted answers

All comments

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"

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.