Blog Categories

Posted by on Aug 14, 2015 in Featured Blogs, Salesforce Formulas Examples, Tutorials | 1 comment

Learn  Salesforce Formulas with Examples-Part 5

Learn Salesforce Formulas with Examples-Part 5

 

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)

1 Comment

  1. Hi Vinay,

    Is there anyway to access custom fiscal years in a formula field. I am trying to create a formula field that contains the end date of the current quarter. Our fiscal starts in

    Q1- Dec-Feb
    Q2 – Mar – May
    Q3- Jun – Aug
    Q4 – Sep – Nov

Post a Reply

Your email address will not be published. Required fields are marked *