Count Patent Expiration date in excel 2

[imme]

Hi,

I am trying to determine Expiration date for patent in excel spreadsheet.
WHAT I HAVE?
File Date : Date when company filed application for patent, i. e.
Publication Date : Date when patent office issue files application as patent i. e.


RULE: patent expire after 20 years from file date or 17 years from publication date, whichever come later.
i. e. If file date is 4/25/1983 and publication date is 1/15/1985, then expiration date will be 4/25/2003

BUT if application date is 4/25/1983 and publication date is 1/15/1987, the expiration date will be 1/15/2007.

PROBLEM:
So, using this criteria, I need to compare file date and publication date first and compare greater one with today's date. So, I can identify expired patents.

Please help me, how can i do this?

 

[SkipVought]

Hi,

If Now - PubDate > 17 Or Now - FileDate > 17 Then
   'houston, we have an expired patient record!

End If

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[imme]

Thank you for reply.

I am new for Excel. Can you please help me to built a formula which i can put in cell to check for 1000s of patent numbers.

Thank you in advance.

 

[xlbo]

inme - this is a VBA forum. If you wanted a formula, you should post in the MSOffice forum.



Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Bowers74]

Hi imme,

I couldn't find your new thread, so I figured I would post my answer to your problem here.

This formula assumes that the application date is located in cell A1 and the publication date is located in cell B1.

=IF(DATE(YEAR(A1)+20,MONTH(A1),DAY(A1))>DATE(YEAR(B1)+17,MONTH(B1),DAY(B1)),DATE(YEAR(A1)+20,MONTH(A1),DAY(A1)),DATE(YEAR(B1)+17,MONTH(B1),DAY(B1)))

I hope this helps you out.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[TonyJollans]

Hi imme,

Mike's formula tells you WHEN they your patents expire. If all you want to know is IF they have expired then you can use DATEDIF.

If File Date is in A1 and Publication Date in B1, then this formula should give you "Expired" (if it is) or blank (if not):

=IF(AND(DATEDIF(A1,NOW(),"Y")>19,DATEDIF(B1,NOW(),"Y")>16),"Expired","")

Enjoy,
Tony

 

[imme]

Thank you. Bowers74 and TonyJollans for your solution. It worked for me and helped me a lot. Thank you very much.