RYG ball to determine priority involving multiple dates

Linda Armstrong

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.

Kelly Moore

Hey @Linda Armstrong

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

Kelly Moore

Hey @Linda Armstrong

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

Linda Armstrong

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!

Kelly Moore

My pleasure. I'm glad it worked for you