IF AND Formula with Multiple Criteria and return Text Value

Tiffany Castro

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

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

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!

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

David Tutwiler

Awesome! Glad it is working