IF AND OR Assistance

Miller39
Miller39 ✭✭
edited 01/06/24 in Formulas and Functions

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

Best Answer

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭✭
    Answer ✓

    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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!