Help with formula field

Question

I have a custom object called Project. Within this object I have two important date fields – Finance Expiry date (Finance_Expiry_Date__c) and Contract Expiry date (Contract_Expiry_Date__c).

These two dates don’t always correlate. Priority is given to the date that is closest to expiry.

I want to create a formula field (Priority Date field) which will show the date that is closest to expiry.

In summary:

• If Finance_Expiry_Date__c is less than Contract_Expiry_Date__c,
  then Finance_Expiry_Date__c becomes the priority date.
• If Contract_Expiry_Date__c is less than Finance_Expiry_Date__c,
  then Contract_Expiry_Date__c becomes the priority date.
• If Finance_Expiry_Date__c is missing,
  then Contract_Expiry_Date__c becomes priority date.
• If Contract_Expiry_Date__c is missing,
  then Finance_Expiry_Date__c becomes priority date.

Answer 1

I recommend you to learn how to use formulas by reading tutorials on trailhead.

Anyway, if you want to check if a field is empty, you can use the ISBLANK() function.Also you can check if a date is inferior to an another by using the < sign. The IF() condition allows to return a date if the condition is true and another date if the condition is fale. Finally, the OR() condition allows you return true if one of the condition is met.

So here's the code for your formula:

IF( OR(Finance_Expiry_Date__c < Contract_Expiry_Date__c, ISBLANK(Contract_Expiry_Date__c)) , Finance_Expiry_Date__c, Contract_Expiry_Date__c)

Note

This formula doesn't include a check to verify if the date is already expired or not. I don't know if you need it in your requirements.

Answer 2

After countless hours, I finally got it to work.

    IF( ISBLANK(Finance_Expiry_Date__c) = TRUE, Contract_Expiry_Date__c, 
    IF(ISBLANK(Contract_Expiry_Date__c) = TRUE,Finance_Expiry_Date__c, 
    IF(Finance_Expiry_Date__c < Contract_Expiry_Date__c, 
    Finance_Expiry_Date__c, 
    IF(Contract_Expiry_Date__c < Finance_Expiry_Date__c, 
    Contract_Expiry_Date__c 

    ,Contract_Expiry_Date__c 
    ))))