0

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

2 Answers 2

0

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.

-1

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 
    ))))
2
  • omg at least check the other answers Mar 27, 2017 at 8:56
  • I did....but it didn't solve the issue as described.
    – Jamrock
    Mar 28, 2017 at 14:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .