# RYG ball to determine priority involving multiple dates

Options

I have a sheet that lists all active contracts for my department which all have expiration dates. However, I am struggling with this sheet because in all calculations I need to consider any extension dates. I do fine with calculations when there is one date involved but when I need to write them to incorporate two and three dates I start to struggle especially when I add MAX into my formulas. I need help with a MAX formula to get the RYG balls to tell me when a Expiration or Extension date is approaching for contracts. Can someone help with a formula to get the following results:

If the Expiration Date or Extension date is approaching within 90 days or less, the priority ball should turn Red.

If the Expiration Date or Extension date is approaching within 180 days, the priority ball should turn Yellow.

If the Expiration Date or Extension date is greater than 180 days, the priority ball should turn Green.

• ✭✭✭✭✭✭
Options

Try this

=IF([Expiration Date]@row = "", "", IF(OR(AND(ISDATE([Extension Date]@row), [Extension Date]@row <= TODAY() + 90), AND([Extension Date]@row = "", [Expiration Date]@row <= TODAY() + 90)), "Red", IF(OR(AND(ISDATE([Extension Date]@row), [Extension Date]@row <= TODAY() + 180), AND([Extension Date]@row = "", [Expiration Date]@row <= TODAY() + 180)), "Yellow", IF(OR(AND(ISDATE([Extension Date]@row), [Extension Date]@row > TODAY() + 180), AND([Extension Date]@row = "", [Expiration Date]@row > TODAY() + 180)), "Green"))))

Will it work for you?

Kelly

• ✭✭✭✭✭✭
Options

Try this

=IF([Expiration Date]@row = "", "", IF(OR(AND(ISDATE([Extension Date]@row), [Extension Date]@row <= TODAY() + 90), AND([Extension Date]@row = "", [Expiration Date]@row <= TODAY() + 90)), "Red", IF(OR(AND(ISDATE([Extension Date]@row), [Extension Date]@row <= TODAY() + 180), AND([Extension Date]@row = "", [Expiration Date]@row <= TODAY() + 180)), "Yellow", IF(OR(AND(ISDATE([Extension Date]@row), [Extension Date]@row > TODAY() + 180), AND([Extension Date]@row = "", [Expiration Date]@row > TODAY() + 180)), "Green"))))

Will it work for you?

Kelly

• Options

Thank you @KELLY MOORE! It worked perfectly. I plugged it into my Smartsheet and it lit up like Christmas and the priority balls were identifying all of the correct contracts. I would have never figured that formula out. Thanks again!

• ✭✭✭✭✭✭
Options

My pleasure. I'm glad it worked for you

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!