Inspired by a lot of problems related to formulas posted in Answers Community ,I decided to put blog series “Learn Salesforce Formulas with Examples“ and here I am with fifth post in the Series.
Main motive for this blog is not to teach you how to begin with formula but how to keep a grip and make a better understanding on it..So stay tuned and I will be providing a lot of examples with explanations in this series which would help you understand more about formulas
Example 21
User wants to create a formula Date field on Opportunity which will return end date of the following quarter after an opportunity has been marked “Closed Won”.
IF(
AND(
ISPICKVAL(StageName, “Closed Won”),
YEAR(CloseDate) = YEAR(TODAY())
),
IF(MONTH(CloseDate)<=3,DATE(YEAR(TODAY()), 6,30),
IF(MONTH(CloseDate)<=6,DATE(YEAR(TODAY()), 9,30),
IF(MONTH(CloseDate)<=9,DATE(YEAR(TODAY()), 12,31),DATE(YEAR(TODAY())+1, 3,31)))),
NULL)
Explanation:
so majorly the above formula explains the return of last date of quarter
Example 22
User needs to add a dynamic hyperlink to a text or formula field.
There is a field- ABC which is added to a SFDC text field called ABC__c from an external system (which is none of the business for us right now)
User needs a way to make the ABC__c field display the ABC number only which is just like a shipment number only but hyperlink to the external system URL
There is one more logic which needs to be checked :
If ABC__c value has 18 characters, Hyperlink =
“http://www.vinaychaturvedi.com/&ABC__c”
If ABC__c value had 12 characters, Hyperlink =
“http://www.vinaychaturvedi.com/&ABC__c&cntry_code=us”
where Field Name is ABC__c
IF(
LEN( ABC__c ) = 18,
HYPERLINK(“http://www.vinaychaturvedi.com/&ABC__c, ‘Info at Vinay Chaturvedi’),
IF(
LEN(ABC__c ) = 12,
HYPERLINK(“http://www.vinaychaturvedi.com/&ABC__c&cntry_code=us”, ‘Info at Vinay US’),
IF(
ISBLANK(ABC__c ), “”,
‘Invalid Hyperlink’
)
)
)
Explanation:
so here we have used Hyperlink function and three level nested IF which check first if the length is 18 digit,If no then checks the length is 15 else,it gives the output as Invalid Hyperlink.
Example 23
User wants a formula for a validation rule when the isvalidated__c checkbox is checked and a text field ABC__c is not equal to ‘Vinay’
AND (
ABC__c <> ‘Vinay’,
isvalidated__c
)
Explanation:
This is a simple formula,just a tip that We can use != or <> for Not Equal to
Example 24
User wants a formula to display the I am working on formula to convert an Event_Date__c value to a “Day of the Week”.
CASE(
MOD(Event_Date__c – DATE( 1900, 1, 7 ), 7 ),
0, “1. Sunday”,
1, “2. Monday”,
2, “3. Tuesday”,
3, “4. Wednesday”,
4, “5. Thursday”,
5, “6. Friday”,
6, “7.Saturday”,
“Error”
)
Explanation:
Here we have used MOD method for subtraction hard coded date for example and use CASE function.
Example 25
User wants to write a Membership Discount formula field that calcuates % based on the Membership Type. Here’s a description of the conditions:
If Membership_Type__c Picklist = Normal, make Membership_Discount__c = 0%
If Membership_Type__c Picklist = VIP, make Membership_Discount__c = 25%
If Membership_Type__c Picklist = VVIP, make Membership_Discount__c = 50%
CASE(
Membership_Type__c ,
“Normal”, 0,
“VIP”, 0.24,
“VVIP”, 0.50,
NULL
)
Explanation:
Please note that the picklist type “Membership_Type__c” doesn’t require TEXT(Membership_Type__c)
