Help with Formula

Eina6216

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

Paul Newcome

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"

Eina6216

Sorry yes you can ignore the first one.

Paul Newcome

Ok. And what are all of the different Statuses?

Eina6216

-1-Hold

0-Backlog

1-Initiate

2-Plan

3-Execute

4-Closed

5-Cancelled

Paul Newcome

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

Eina6216

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

Eina6216

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

Paul Newcome

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

Eina6216

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.