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.
  • 4
    What did you try so far? – Martin Lezer Mar 23 '17 at 14:04
  • IF (Finance_Expiry_Date__c < Contract_Expiry_Date__c, Finance_Expiry_Date__c, Contract_Expiry_Date__c) – Jamrock Mar 23 '17 at 16:12

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)


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.


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, 
    IF(Contract_Expiry_Date__c < Finance_Expiry_Date__c, 

  • omg at least check the other answers – Martin Lezer Mar 27 '17 at 8:56
  • I did....but it didn't solve the issue as described. – Jamrock Mar 28 '17 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.