Conditional Formula Help

a.gaynor

Hello!

I am trying to write a formula that is based on status and end date columns that will return an "in progress" or "overdue" status. I need it to contemplate the following:

If the drop down for the status column is one of the following: blank, "not started," "on hold," "blocked," or "in progress"

AND the end date

Based on the formula above, the output should be one of the two below:

is in the past = overdue

is in the future = in progress

As a side note, I want the formula to exclude "completed" and "out of scope" in the status column.

Here are the two formulas I've tried so far, but I can't get them to work:

=IF([Status Update]@row = "In Progress", IF(End@row > TODAY(), "In Progress", "Overdue"))

=IF(AND([Status Update]@row <> "Complete, Out of Scope", End@row < TODAY()), "Overdue", "N/A")

The columns I am working with are:

Status Update - Drop down list

-Values are: Not Started, In Progress, Complete, Out of Scope, Blocked, On Hold

End - Date/Time

Scott Peters

Hi @a.gaynor - We do something very similar in our project plan template, using nested if combined with "or" statements. You could try something like this?

=if(or([Status Update]@row="Complete", [Status Update]@row = "Out of Scope"), "", if(End@row>Today(), "On Track", "Overdue")

Here's a snip of our actual formula, which accounts for when the End Date is not populated:

IF(OR(Status@row = "Complete", Status@row = "Canceled"), "Closed", IF(ISBLANK([Due Date]@row), "Need Date", IF([Due Date]@row < TODAY(), "Overdue", IF([Due Date]@row < TODAY(7), "This Week", IF([Due Date]@row < TODAY(14), "Next Week", "Future")))), "")

I hope this helps!

Danielle Arteaga

I think this formula should work for you if placed in the Overdue Tasks column:

=IF(OR([Status Update]@row = "Complete", [Status Update]@row = "Out of Scope"), "Not Applicable", IF(End@row > TODAY(), "In Progress", "Overdue"))

Nested formulas resolve in order until the first "true" result. So, first, the formula looks for anything in the Status Update column that is "Complete" or "Out of Scope." If that is true, the result is "Not Applicable" (or "N/A"). If the result is false, it then looks at the date in the End column. If that date is greater than today's date, the result is "In Progress." If not, the result is "Overdue." This would apply for any status in the Status Update column that is not "Complete" or "Out of Scope."

I tested this here:

Scott Peters

Hi @a.gaynor - We do something very similar in our project plan template, using nested if combined with "or" statements. You could try something like this?

=if(or([Status Update]@row="Complete", [Status Update]@row = "Out of Scope"), "", if(End@row>Today(), "On Track", "Overdue")

Here's a snip of our actual formula, which accounts for when the End Date is not populated:

IF(OR(Status@row = "Complete", Status@row = "Canceled"), "Closed", IF(ISBLANK([Due Date]@row), "Need Date", IF([Due Date]@row < TODAY(), "Overdue", IF([Due Date]@row < TODAY(7), "This Week", IF([Due Date]@row < TODAY(14), "Next Week", "Future")))), "")

I hope this helps!

Danielle Arteaga

I think this formula should work for you if placed in the Overdue Tasks column:

=IF(OR([Status Update]@row = "Complete", [Status Update]@row = "Out of Scope"), "Not Applicable", IF(End@row > TODAY(), "In Progress", "Overdue"))

Nested formulas resolve in order until the first "true" result. So, first, the formula looks for anything in the Status Update column that is "Complete" or "Out of Scope." If that is true, the result is "Not Applicable" (or "N/A"). If the result is false, it then looks at the date in the End column. If that date is greater than today's date, the result is "In Progress." If not, the result is "Overdue." This would apply for any status in the Status Update column that is not "Complete" or "Out of Scope."

I tested this here:

a.gaynor

Thank you both very much!! Both answers worked beautifully!