Trying to create a formula field in Salesforce to

2019-07-23 18:12发布

问题:

Trying to create a formula field in salesforce to calculate the average number of days taken from Close date to (either of the 2 other dates). E.g

Close date
Email start date live date

formula I'm trying to write is close date - (earliest of the email start date or the live date).

Help will be appreciated. thanks

回答1:

There's a MAX() function available but it accepts only numbers, not dates. So have a look at these and pick whichever you're feeling more comfortable with ;)

(Formula type must be of type Number, call it "Lifespan (in days)" or something)

IF(Email_Start_Date__c > Live_Date__c, Email_Start_Date__c, Live_Date__c) - Close_Date__c

or

MAX(Email_Start_Date__c - Close_Date__c, Live_Date__c - Close_Date__c)

Edit to incorporate Priyanka's problem from the comments: If you have several dates you want to fall back on (if one is null, try another one) it's best to use BLANKVALUE function to avoid long chains of IF statements:

BLANKVALUE(Over_ride_Date__c, 
    BLANKVALUE(Email_Start_Date__c, 
        BLANKVALUE(Live_Date__c, 
            BLANKVALUE(Estimated_Email_Start_Date__c, Estimated_Go_Live_Date__c)
        )
    )
)

You can also combine it with the trick with IF's to select lowest one, for example:

BLANKVALUE(Over_ride_Date__c, 
    IF(NOT(ISBLANK(Email_Start_Date__c)) && NOT(ISBLANK(Go_Live_Date__c)), 
        IF(Email_Start_Date__c < Go_Live_Date__c, Email_Start_Date__c , Go_Live_Date__c ),
        BLANKVALUE(Estimated_Email_Start_Date__c, Estimated_Go_Live_Date__c)
    )
)

This will check the override date first. If it's null but both email start & go live dates are set - will select smaller of the 2. If any of them is blank - will keep falling back to check estimated dates and finally will give up if with null if everything was null.

But I have to say if you really need to write such convoluted things - you're doing something wrong. Force your users to start filling in some dates earlier, it's a sign of very poor data quality...



标签: salesforce