Help with Formula

Options

Please help! I've been struggling with this for a while.

I'm trying to get a formula to return "Yes" or "No" based on several variables.

If the start date is less than today and status is 0-Backlog, Yes

End date is less than today + 30 and status is not equal to 4-Closed, Yes

Start date is less than today + 30 and status is 0-Backlog, Yes

Start date is less than today and Status is not equal to 4-Closed, Yes

Start date is less than today and Status is not equal to 5-Cancelled, Yes

Start date is less than today and Status is not equal to -1-Hold, Yes

Status is = to 1-Initiate, 2-Plan or 3-Execute, Yes

Otherwise, No

=IF(OR(AND([Start date]358 < TODAY(), Status358 = "0-Backlog")), (AND([End date]358 < TODAY(30), Status358 <> "4-Closed")), (AND([Start date]358 < TODAY(30), Status358 = "0-Backlog")), (AND([Start date]358 < TODAY(), Status358 <> "4-Closed")), (AND([Start date]358 < TODAY(), Status358 <> "5-Cancelled")), (AND([Start date]358 < TODAY(), Status358 <> "-1-Hold")), Status358 = "1-Initiate",

Status358 = "2-Plan", Status358 = "3-Execute"), "Yes", "No")

This returns #UNPARSEABLE.

This value works somewhat but still returns "Yes" if the Status is 5-Cancelled or 4-Closed or -1-Hold.

=IF(OR((AND([Start date]347 < TODAY(), Status347 = "0-Backlog")), (AND([End date]347 < TODAY(30), Status347 <> "4-Closed")), Status347 = "1-Initiate", Status347 = "2-Plan", Status347 = "3-Execute", (AND([Start date]347 < TODAY(30), Status347 = "0-Backlog")), (AND([Start date]347 < TODAY(0), Status347 <> "4-Closed", Status347 <> "5-Cancelled", Status347 <> "-1-Hold"))), "Yes", "No")

Tags:

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!