IF AND OR Assistance

Miller39

I'm trying to write a formula that determines if a task is at risk, so the [At Risk} column can be updated for automated dashboard reporting.

The formula's I'm trying to combine, logically, flow like this:

If [Start Date] is Less than or equal to today AND [Status] is not equal to "Complete" or "On track", then [At Risk] = Y.

Or, if [End Date] is Less than or equal to today and [Status] is not equal to Complete, then [At Risk} = Y.

I can get one leg of the first formula to work independently, but I'm failing to understand how to combine both "Complete" and "On Track" on the first leg. And then, I'm failing to understand how to add the [End Date] segment of the formula to the first, so it checks both for [Risk] being "Y".

I can get to the point were these two formula's work:

=IF(AND([Start Date]@row <= TODAY(), (Status@row <> "Complete")), "Y", " ")

=IF(AND([End Date]@row <= TODAY(), (Status@row <> "Complete")), "Y", " ")

DKazatsky2

Hi @Miller39,

See if this does what you need.

=IF(AND([Start Date]@row <= TODAY(), Status@row <> "Complete", Status@row <> "On Track"), "Y", IF(AND([End Date]@row <= TODAY(), (Status@row <> "Complete")), "Y", " "))

Hope this helps,

Dave

DKazatsky2

Hi @Miller39,

See if this does what you need.

=IF(AND([Start Date]@row <= TODAY(), Status@row <> "Complete", Status@row <> "On Track"), "Y", IF(AND([End Date]@row <= TODAY(), (Status@row <> "Complete")), "Y", " "))

Hope this helps,

Dave

Miller39

Thank you very much @DKazatsky2. Excellent solution. I am very grateful.