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

Tracy Hockenberry

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

Paul Newcome

Try something like this:

=IF([Provisional Patent Expiration Date]@row = "", "Gray", IF([Provisional Patent Expiration Date]@row<= TODAY(-270), "Red", IF([Provisional Patent Expiration Date]@row<= TODAY(-180), "Yellow", "Green")))

Mike TV

@Tracy Hockenberry

You need to add the days + or - TODAY into the parenthesis after the word Today. Additionally, you need the colors to have quotes around them. Also, you need your ISBLANK to have an end parenthesis after telling it which cell to evaluate if is blank. Also, you had too many AND functions that weren't being used. So like this:

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

Tracy Hockenberry

@Paul Newcome @Mike TV Thank you both so much for the above guidance - the gray symbols are working correctly w/both formulas, but my green/yellow/reds are not. I am trying to play w/the formula now but haven't yet figured it out. In the screenshot, the patent that was filed on 11/1/22, and doesn't expire until 11/1/23 should be green as it is within 6 months; the ones below that are also not displaying correctly yet (the January one should be red for example).

Screenshot 2022-11-11 153300.png

Mike TV

@Tracy Hockenberry

Try this one:

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

Tracy Hockenberry

They still don't seem to be working? The January one is correct at red, but the April one should be yellow as it is past 6 months but under 9 months from the file date. The same with the June and July ones - they should be green. this formula is so difficult for some reason?! I really appreciate you trying to troubleshoot this with me.

Mike TV

@Tracy Hockenberry

It's not working correctly because until your last screenshot you hadn't properly divulged what you really wanted the formula to do. You're not wanting a formula based on the Patent Expiration Date like you were trying to write in your original formula. You're wanting a formula based on the Patent Filed date. That's a whole different thing. I'll try to work on it some more and see if I can get it working for you.

Mike TV

@Tracy Hockenberry

Does this one work for you?

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

Tracy Hockenberry

@Mike TV Thank you for the feedback and I do apologize for anything that was confusing. My initial thoughts were that this could be written using either - so if using the expiration date (which is one year from the file date), we could look at how far away from that date we were (were we 3 months out, 6 months out, etc.). The other line of thinking could be to use the file data and how far past that date we currently are (so from the filing date to the 3 month mark, to the 9 month mark, etc.). I thought either way would get me where I was trying to go (the formula would just need written accordingly). It is now showing properly! :) Thank you so very much!

Paul Newcome

It can also be simplified quite a bit (and written off of the expiration date) like so:

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