IF AND OR Assistance

Options
✭✭
edited 01/06/24

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!