Set Priority Number based on Date and Aging?

brinaldo

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!

Michael Culley

@brinaldo

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

Michael Culley

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 Sides

Switch the order of your operations. Go from the bottom up. Check for priority 4 first, then 3, then 2, then 1. Does that make sense?

Ryan

Michael Culley

Ryan's way is the easier way.

brinaldo

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

Michael Culley

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

brinaldo

=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.

Michael Culley

I'm going to recreate it on my end to see what I discover. I'll be right back.

Michael Culley

Can you give us some sample inputs that should be giving you a 3 or a 4?

Michael Culley

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.

Michael Culley

@brinaldo

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

brinaldo

@Michael Culley

That looks like it did the trick!! Thanks so much!!