How to write a formula based on 3,6,9 months from current date, using G/R/Y/G symbols?

Hi,

I am trying to write a formula that uses the gray, green, yellow, red symbols to show how close we are getting to something expiring. If the Patent has not yet been filed (date is blank) this would be the gray symbol. If it has been filed and is under 6 months old, green; if it is between 6 months - 9 months from the filing date, yellow, and over 9 months red. I am struggling to figure out the formula. Any suggestions? Thanks in advance!

=IF(ISBLANK([Provisional Patent Expiration Date]@row, Gray, IF(AND([Provisional Patent Expiration Date]@row < TODAY() + 180, Green, IF(AND([Provisional Patent Expiration Date >= TODAY() +180, Yellow, IF(AND([Provisional Patent Expiration Date]@row >= TODAY () + 270, Red))))

Tags:

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!