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
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...