Formula Not Executing Correctly

Question

All below conditions are executing properly except 2 (no output comming),can you say what's wrong?...

    EP DATE    App DATE    ISSUE DATE   RESULT  
    6/9/1995    6/6/1994    6/9/1997    9-Jun-15    
                8/25/2004   8/30/2005   25-Aug-24   ---->2
    2/7/1995    6/7/1995    7/7/1995    7-Feb-15    
    9/30/2005   1/6/2009    6/7/2028    30-Sep-25   
                8/25/1994   8/30/2000   30-Aug-17   
    8/24/1994   8/25/1994   8/30/2000   30-Aug-17   
    8/24/1990   8/25/1994   8/30/1995   30-Aug-12   
    8/24/1993   8/25/1994   8/30/1995   24-Aug-13   

The term of a patent is 20 years from the earliest claimed priority date (or longer of 17 years from the issue date or 20 years from the earliest claimed priority date if the patent application was filed on or before June 8, 1995). 35 USC § 154.

Below is the formula...

   IF(Application_Date__c >= DATE(1995,8,6), 

      DATE( YEAR( Earliest_Priority_Date__c ) + 20 , 
            MONTH(Earliest_Priority_Date__c ) , 
            DAY(Earliest_Priority_Date__c ) ) ,

      IF(DATE( YEAR( Earliest_Priority_Date__c ) + 20 , 
               MONTH(Earliest_Priority_Date__c ) , 
               DAY(Earliest_Priority_Date__c ) ) > 
         DATE( YEAR( Issue_Date_of_Patent__c ) + 17 , 
               MONTH(Issue_Date_of_Patent__c) , 
               DAY(Issue_Date_of_Patent__c) ), 

         DATE( YEAR( Earliest_Priority_Date__c ) + 20 , 
               MONTH(Earliest_Priority_Date__c ) , 
               DAY(Earliest_Priority_Date__c ) ),

         DATE( YEAR( Issue_Date_of_Patent__c ) + 17 , 
               MONTH(Issue_Date_of_Patent__c) , 
               DAY(Issue_Date_of_Patent__c) ))
      )
  )

Answer 1

All of your logic that calculates the date for records for which Application_Date__c >= DATE(1995,8,6) is true relies upon the Earliest_Priority_Date__c being populated.

You have two records in the data set you shared where that field is blank, Rows 2 and 5. However, Row 5 is pre-1995-08-06, and hence gets a date based on Issue_Date_of_Patent__c, which is populated. Note that you may have another subtle issue here, since your comparisons between the issue date and earliest priority date aren't accounting for what should happen if either date is blank.

Since Row 2 is post-1995-08-06, it falls into your conditional based on Earliest_Priority_Date__c. But since that field is null, none of the conditionals pass, and you get a blank (null) output.

The fix for this is to use the ISBLANK() function early on in your logic, around the first arm of your outmost IF:

  IF(!ISBLANK(Earliest_Priority_Date__c),
        DATE( YEAR( Earliest_Priority_Date__c ) + 20 , 
        MONTH(Earliest_Priority_Date__c ) , 
        DAY(Earliest_Priority_Date__c ) ) ,
        SOMETHING_DEFAULT_HERE),

to check whether Earliest_Priority_Date__c is populated, if you want to supply a default value for that case. If you don't want to populate for that case, you can leave the formula as-is.