Formulas are a great way to automate field data, create Validation Rules, and define workflow criteria. Here are some examples of useful formulas:
Formula to assign a value based on the value of another field (If/then statements)
IF (AnnualRevenue > 10000000, “Hot”, “Warm”)
Translation: If the Annual Revenue value is greater than 10 million dollars then the value of the field will be “Hot”. If the Annual Revenue value is less than 10 million dollars then the value of the field will be “Warm”
IF(ISBLANK(BillingState), “None”,
IF(CONTAINS(“AK:AZ:CA:HA:NV:NM:OR:UT:WA”, BillingState), “West”,
IF(CONTAINS(“CO:ID:MT:KS:OK:TX:WY”, BillingState), “Central”,
IF(CONTAINS(“CT:ME:MA:NH:NY:PA:RI:VT”, BillingState), “East”,
IF(CONTAINS(“AL:AR:DC:DE:FL:GA:KY:LA:MD:MS:NC:NJ:SC:TN:VA:WV”,
BillingState), “South”,
IF(CONTAINS(“IL:IN:IA:MI:MN:MO:NE:ND:OH:SD:WI”, BillingState), “North”,
“Other”))))))
Translation: If the BillingState value is Blank then the value of the field will be “None”. If the BillingState value is one of the following: AK:AZ:CA:HA:NV:NM:OR:UT:WA the value of the field will be “West”. If the BillingState value is one of the following: CO:ID:MT:KS:OK:TX:WY the value of the field will be “Central”. If the BillingState value is one of the following: CT:ME:MA:NH:NY:PA:RI:VT the value of the field will be “East”. If the Billing State value is one of the following: AL:AR:DC:DE:FL:GA:KY:LA:MD:MS:NC:NJ:SC:TN:VA:WV the value of the field will be “South”. If the BillingState value is one of the following: IL:IN:IA:MI:MN:MO:NE:ND:OH:SD:WI the value of the field will be “North”. If the BillingState value is none of the above, the value of the field will be “Other”.
Commission Amounts for Opportunities
IF(ISPICKVAL(StageName, “Closed Won”), ROUND(Amount *0.02, 2), 0)
Translation: If the StageName picklist value is “Closed Won”, then the value of the field will be 2% of the Amount field value. It will be rounded up to 2 decimal places.
Contact Preferred Phone
CASE(Preferred_Phone__c, “Work”, “w. ” & Phone, “Home”, “h. ” & HomePhone, “Mobile”, “m. ” & MobilePhone, “No Preferred Phone”)
Translation: When the Preferred Phone value is “Work” the value of the field will be “w “ plus the value in the Phone field. When the Preferred Phone value is “Home” the value of the field will be “h “ plus the value in the HomePhone field. When the Preferred Phone value is “Mobile” the value of the field will be “m “ plus the value in the MobilePhone field. When the Preferred Phone value is not any of the above, the value of the field will be “No Preferred Phone“.
Unformatted Phone Number for North America
IF(Country_Code__c = “1”, MID( Phone ,2, 3) & MID(Phone,7,3) & MID(Phone,11,4), Phone)
Translation: If the Country_Code__c = “1” (North America), then put the following string of text together: Starting at the second character of the Phone field value get next 3 characters. At the seventh character, get the next 3 characters. Starting at the eleventh character, get the next 4 characters.
| ( | 5 | 5 | 5 | ) | 5 | 5 | 5 | – | 5 | 5 | 5 | 5 | ||
| Starting Point | 2 | 7 | 11 |
Finding the Day, Month, or Year from a Date
Use the following Date functions:
DAY( date ) – returns 1 – 7 depending on the day of the week the date falls on.
MONTH( date ) – returns 1 – 12 depending on the month of the year the date falls on.
YEAR( date ) – returns the year of the date in a 4 digit numerical value.
* Replace date with a value of type Date (e.g. TODAY()).
Displaying the Month as a String Instead of a Number
CASE(
MONTH( date ),
1, “January”,
2, “February”,
3, “March”,
4, “April”,
5, “May”,
6, “June”,
7, “July”,
8, “August”,
9, “September”,
10, “October”,
11, “November”,
“December”
)
Translation: Based on the number of the month in the Date field value, the full month name will display.
