# Help with Formula

Options

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:

• ✭✭✭✭✭✭
Options

What are all of the different Statuses possible?

I noticed that you have two entries that overlap. The way they are written, the first is covered by the second which makes it redundant. Did you mean something different, or can we ignore the first?

Start Date less than today and Status equals "0-Backlog"

Start Date less than today + 30 and Status equals "0-Backlog"

• Options

Sorry yes you can ignore the first one.

• ✭✭✭✭✭✭
Options

Ok. And what are all of the different Statuses?

• Options

-1-Hold

0-Backlog

1-Initiate

2-Plan

3-Execute

4-Closed

5-Cancelled

• ✭✭✭✭✭✭
Options

Give this one a go. It covers everything exactly as you have listed (minus the first one).

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

• Options

It is working for all of them except it is still returning Yes for 5-Cancelled. Thanks for all your help!

• Options

I think it is returning Yes for Cancelled if the start date is earlier than Today.

• ✭✭✭✭✭✭
Options

Please verify. Also double check that I entered the "specific text" correctly within the formula. The BOLD portion below is where it should be EXCLUDING "5-Cancelled" with a Start Date of earlier than today. It may be because I missed a space somewhere such as before or after the hyphen as it is looking for an exact match on that string.

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

• Options

I did some more testing on this. This returns Yes if the [Start Date] is earlier than TODAY and the status is "5-Cancelled". If I change the [Start Date] to after TODAY and the status is "5-Cancelled", it returns no. I'd like to return No if status is "5-Cancelled", regardless of Start Date.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!