# 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!

• ✭✭✭✭✭

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

• ✭✭✭✭✭

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?

• ✭✭✭✭✭✭

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

Ryan Sides

Come Say Hello!

• ✭✭✭✭✭

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.

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.

• ✭✭✭✭✭
edited 10/13/22

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

• ✭✭

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!