Help with Formula
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")
Answers
-
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"
-
Sorry yes you can ignore the first one.
-
Ok. And what are all of the different Statuses?
-
-1-Hold
0-Backlog
1-Initiate
2-Plan
3-Execute
4-Closed
5-Cancelled
-
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")
-
It is working for all of them except it is still returning Yes for 5-Cancelled. Thanks for all your help!
-
I think it is returning Yes for Cancelled if the start date is earlier than Today.
-
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")
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!