Set Priority Number based on Date and Aging?
Hi there,
Struggling a bit with an If/then formula to create a series of priorities.
Priority 1: Date is within 21 days OR Aging is more than 300 days
Priority 2: Date is within 35 days OR Aging is less than 299 days
Priority 3: Date is within 56 days OR Aging is less than 199 days
Priority 4: Date is greater than 56 days OR Aging is less than 100 days
The formula I put together is the following, but is only yielding 1's and 2's, even though the data set should also be yielding 3's and 4's.
=IF(OR([QA Disposition Date]@row < TODAY(21), Aging@row > 300), "1", IF(OR([QA Disposition Date]@row < TODAY(35), Aging@row < 299), "2", IF(OR([QA Disposition Date]@row < TODAY(56), Aging@row < 199), "3", IF(OR([QA Disposition Date]@row > TODAY(56), Aging@row < 100), "4"))))
Thanks so much!
Best Answer
-
Ok let's try this lol
=IF(OR([QA Disposition Date]@row < TODAY(21), Aging@row > 300), 1, IF(OR(AND([QA Disposition Date]@row >= TODAY(21), [QA Disposition Date]@row < TODAY(35)), AND(Aging@row >= 200, Aging@row <= 299)), 2, IF(OR(AND([QA Disposition Date]@row > TODAY(35), [QA Disposition Date]@row <= TODAY(56)), AND(Aging@row >= 100, Aging@row <= 199)), 3, 4)))
Answers
-
The problem seems to be that conditions that meet requirements for Priority 3 can also meet the requirements for Priority 2, so it'll yield priority 2 since that comes first in the formula.
Perhaps you need to add an AND to your formula for each priority so that priority 2 says something like if date is greater than 21 days and less than 35 days. Same with Priority 3. If date is greater than 35 days and less than 56 days.
Does that make sense?
-
Ryan's way is the easier way.
-
Thanks @Ryan Sides and @Michael Culley for the reply!
If I reverse the order:
=IF(OR([QA Disposition Date]@row > TODAY(56), Aging@row < 100), "4", IF(OR([QA Disposition Date]@row < TODAY(56), Aging@row < 199), "3", IF(OR([QA Disposition Date]@row < TODAY(35), Aging@row < 299), "2", IF(OR([QA Disposition Date]@row < TODAY(21), Aging@row > 300), "1"))))
Then it yields 3's and 4's.
Michael, your point about adding an AND may be accurate. What would that look like?
Priority 1: Date is within 21 days OR Aging is more than 300 days
Priority 2: Date is between 21 and 35 days OR Aging is between 200 and 299 days
Priority 3: Date is between 35 and 56 days OR Aging is between 100 and 199 days
Priority 4: Date is greater than 56 days OR Aging is less than 100 days
-
I would do:
=If(OR([QA Disposition Date]@row < TODAY(21),aging@row > 300,1,IF(OR(AND([QA Disposition Date]@row < TODAY(35),[QA Disposition Date]@row >= TODAY(21)),Aging@row < 299), "2",IF(OR(AND([QA Disposition Date]@row < TODAY(56),[QA Disposition Date]@row >= TODAY(35)), Aging@row < 199), "3","4")))
-
=IF(OR([QA Disposition Date]@row < TODAY(21), Aging@row > 300), "1", IF(OR(AND([QA Disposition Date]@row < TODAY(35), [QA Disposition Date]@row >= TODAY(21)), Aging@row < 299), "2", IF(OR(AND([QA Disposition Date]@row < TODAY(56), [QA Disposition Date]@row >= TODAY(35)), Aging@row < 199), "3", "4")))
back to yield 1's and 2's only.
-
I'm going to recreate it on my end to see what I discover. I'll be right back.
-
Can you give us some sample inputs that should be giving you a 3 or a 4?
-
Ohhh I see it's because of the aging is also the issue. whenever the aging is less than 299 it satisfies the priority 2 no matter what the date is.
I'll come up with the fix.
-
Ok let's try this lol
=IF(OR([QA Disposition Date]@row < TODAY(21), Aging@row > 300), 1, IF(OR(AND([QA Disposition Date]@row >= TODAY(21), [QA Disposition Date]@row < TODAY(35)), AND(Aging@row >= 200, Aging@row <= 299)), 2, IF(OR(AND([QA Disposition Date]@row > TODAY(35), [QA Disposition Date]@row <= TODAY(56)), AND(Aging@row >= 100, Aging@row <= 199)), 3, 4)))
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!