IF AND Formula with Multiple Criteria and return Text Value

I have a percentage completion column, status column, and finish date column.

I would like a formula as follows to enter a text value into the status column…

IF percentage = 100 and Finish date = Today then enter "Complete" if true and "In Progress" if not.

If percentage > 100 and Finish date > Today then enter "Not Started" if true and "Incomplete" if not.

This is what I have so far…What am I doing wrong???

=IF(AND(Percentage@row = 1, [Finish Date]@row = TODAY()), "Completed", "In Progress", IF(AND(Percentage@row > 1, [Finish Date]@row < TODAY()), "Incomplete", "Not Started"))

Find more posts tagged with

Formulas

Accepted answers

Tiffany Castro

WE DID IT, DAVID!! THANK YOU!

=IF(Percentage@row = 1, "Completed", IF([Finish Date]@row = TODAY(), "In Progress", IF(AND(Percentage@row = 0, [Finish Date]@row > TODAY()), "Not Started", IF(AND(Percentage@row < 1, [Finish Date]@row < TODAY()), "Incomplete", "In Progress"))))

All comments

David Tutwiler

You need to Nest the two IF statements. The IF statement has 3 parameters - Condition (thing that is evaluated), Positive Outcome (if true, what happens), Negative Outcome (if false what happens). You currently have 4+ parameters in 1 IF statement. So you'll need to Nest a few IF statements together. Something like this should work.

=IF(AND(Percentage@row = 1, [Finish Date]@row = TODAY()), "Completed", IF(OR(Percentage@row < 1, [Finish Date]@row > TODAY()), "In Progress", IF(AND(Percentage@row > 1, [Finish Date]@row < TODAY()), "Incomplete", "Not Started")))

Tiffany Castro

This is what I would really need

IF percentage = 1, "Completed" (no matter what the date is)

IF percentage > 1, AND Finish date is within 2 days of finish date, "In Progress"

IF percentage > 1, AND finish date < Today + 2 days, "Incomplete"

IF percentage > 1, and Finish date > Today, "Not Started"

Tiffany Castro

Thanks for the quick response…tbh I would rather it be like

If percentage = 1 (100%) = "completed" and not have it dependent on a date while the other ones can be.

If percentage > 1 and Finish date = < TODAY (+2 days) = In progress

If percentage > 1 and finish date = > TODAY (+2 days) = Incomplete

If percentage > 1 and Finish date > Today = Not Started

Does that make more sense?

Tiffany Castro

My error says UNPARSEABLE

=IF(AND(Percentage@row = 1, [Finish Date]@row =< TODAY()), "Completed" IF(OR(Percentage@row > 1, [Finish Date]@row =< TODAY(2)), "In Progress", IF(OR(Percentage@row > 1, [Finish Date]@row < TODAY()), "Not Started", IF(OR(Percentage@row > 1, [Finish Date]@row < TODAY(2)), "Incomplete"

David Tutwiler

I think you have a logical problem with your description because a few of your logic wants overlap. I'll describe them below, but this is a formula that has your written requests exactly as they are written.

=IF(Percentage@row = 1, "Complete", IF(AND(Percentage@row > 1, [Finish Date]@row < TODAY(2)), "In Progress", IF(AND(Percentage@row > 1, [Finish Date]@row > TODAY(2)), "Incomplete", IF(AND(Percentage@row > 1, [Finish Date]@row > TODAY(), "Not Started")))))

In wish number 3 and wish number 4, you have that the percentage is greater than 100% and that the date is greater than today. I don't think you'll ever get Not Started with those parameters, I think it will always say Incomplete. Otherwise it should work.

Tiffany Castro

I think I am almost there…but the ones that should say "Not Started" / In Progress are blank

=IF(Percentage@row = 1, "Completed", IF([Finish Date]@row = TODAY(2), "In Progress", IF(AND(Percentage@row > 1, [Finish Date]@row > TODAY()), "Not Started", IF(AND(Percentage@row < 1, [Finish Date]@row < TODAY(2)), "In Complete"))))

Tiffany Castro

WE DID IT, DAVID!! THANK YOU!

David Tutwiler

Awesome! Glad it is working

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.