Salesforce Useful Formula Fields
DOWNLOAD
First things first !
To download this implementation guide, click the download button below.
If you need more information about the implementation guide, you can read the Table of Contents below.
EXAMPLES OF ADVANCED FORMULA FIELDS
Summary
Review examples of formula
fields for various types of
apps that you can use and
modify for your own
purposes
Review examples of formula fields for various types of apps that you can use and modify for your own
purposes
This document contains custom formula samples for the following topics For details about using the
functions included in these samples see Formula Operators and Functions by Context on page
Sample Account Management Formulas
Use these formulas to manage account details
For details about using the functions included in these samples see Formula Operators and Functions by
Context on page
Account Rating
This formula evaluates Annual Revenue Billing Country and Type and assigns a value
of Hot Warm or Cold
IF AND AnnualRevenue
CONTAINS CASE BillingCountry United States US America US
USA US NA US
IFISPICKVALType Manufacturing Partner Hot
IFOR ISPICKVAL Type Channel PartnerReseller
ISPICKVALType Installation Partner Warm Cold
Cold
In addition you can reference this Account Rating formula field from the contact object using crossobject
formulas
AccountAccountRatingc
Account Region
This formula returns a text value of North South East West or Central based on the Billing
StateProvince of the account
IFISBLANKBillingState None
IFCONTAINSAKAZCAHANVNMORUTWA BillingState West
IFCONTAINSCOIDMTKSOKTXWY BillingState Central
IFCONTAINSCTMEMANHNYPARIVT BillingState East
IFCONTAINSALARDCDEFLGAKYLAMDMSNCNJSCTNVAWV
BillingState South
IFCONTAINSILINIAMIMNMONENDOHSDWI BillingState North
Other
Last updated November
Examples of Advanced Formula Fields
Sample Account Media Service Formulas
Contract Aging
This formula calculates the number of days since a contract with an account was activated If the contract
Status isnt Activated this field is blank
IFISPICKVALContractStatusc Activated
NOW ContractActivatedDatec null
Sample Account Media Service Formulas
Formulas to link to specific search sites and media accounts
For details about using the functions included in these samples see Formula Operators and Functions by
Context on page
BBC News Search
This formula creates a link to a BBC news search site based on the Account Name
HYPERLINK
httpwwwbbccouksearchnewsqName
BBC News
Bloomberg News Search
This formula creates a link to an accounts ticker symbol on the Bloomberg website
HYPERLINK
httpwwwbloombergcommarketssymbolsearchqueryTickerSymbol
Bloomberg News
CNN News Search
This formula creates a link to a CNN news search site using the Account Name
HYPERLINK
httphttpwwwcnncomsearchqueryName
CNN News
MarketWatch Search
This formula creates a link to an accounts ticker symbol on the Marketwatchcom website
HYPERLINK
httpwwwmarketwatchcominvestingstockTickerSymbol
Marketwatch
Examples of Advanced Formula Fields
Sample Case Management Formulas
Google Search
This formula creates a link to a Google search site using the Account Name
HYPERLINK
httpwwwgooglecomqName
Google
Google News Search
This formula creates a link to a Google news search site using the Account Name
HYPERLINK
httpnewsgooglecomnewssearchenqName
Google News
Yahoo Search
This formula creates a link to a Yahoo search site using the Account Name
HYPERLINK
httpsearchyahoocomsearchpName
Yahoo Search
Yahoo News Search
This formula creates a link to a Yahoo news search site using the Account Name
HYPERLINK
httpnewssearchyahoocomsearchnewspName
Yahoo News
Sample Case Management Formulas
Use these formulas to manage case details
For details about using the functions included in these samples see Formula Operators and Functions on
page
Autodial
This formula creates a linkable phone number field that automatically dials the phone number when
clicked In this example replace servername and call with the name of your dialing tool
and the command it uses to dial The merge field Id inserts the identifier for the contact lead or account
record The first Phone merge field tells the dialing tool what number to call and the last Phone merge
field uses the value of the Phone field as the linkable text the user clicks to dial
HYPERLINKhttpservernamecallid Id phone
Phone Phone
Examples of Advanced Formula Fields
Sample Commission Calculations Formulas
Case Categorization
This formula displays a text value of RED YELLOW or GREEN depending on the value of a case age
custom text field
IFDaysOpenc RED
IFDaysOpenc YELLOW
GREEN
Case Data Completeness Tracking
This formula calculates the percentage of specific custom fields that contain data The formula checks the
values of two custom number fields Problem Num and Severity Num If the fields are empty
the formula returns the value The formula returns a value of for each field that contains a value
and multiplies this total by fifty to give you the percentage of fields that contain data
IFISBLANKProblemNumc IFISBLANKSeverityNumc
Suggested Agent Prompts
This formula prompts an agent with crosssell offers based on past purchases
CASEProductPurchc
Printer Extra toner cartridges Camera Memory cards
Special of the day
Suggested Offers
This formula suggests a product based on the support history for a computer reseller When the Problem
custom field matches a field the formula field returns a suggestion
CASEProblemc
Memory Suggest new memory cards Hard Drive failure Suggest
new hard drive with tape backup
Sample Commission Calculations Formulas
Use these formulas to calculate commission amounts
For details about using the functions included in these samples see Formula Operators and Functions on
page
Commission Amounts for Opportunities
The following is a simple formula where commission is based on a flat of the opportunity Amount
IFISPICKVALStageName Closed Won
ROUNDAmount
Examples of Advanced Formula Fields
Sample Contact Management Formulas
This example calculates the commission amount for any opportunity that has a Closed Won stage The
value of this field is the amount times for any closed or won opportunity Open or lost opportunities
have a zero commission value
Commission Deal Size
This formula calculates a commission rate based on deal size returning a commission rate for deals
over and an commission rate for smaller deals
IFAmount
Commission Greater Than or Equal To
This formula assigns the YES value with a commission greater than or equal to one million Note this field
is a text formula field that uses a custom currency field called Commission
IFCommissionc
YES NO
Commission Maximum
This formula determines what commission to log for an asset based on which is greater the users
commission percentage of the price the price times the discount percent stored for the account or
dollars This example assumes you have two custom percent fields on users and assets
MAXUserCommissionPercentc Price
Price AccountDiscountc
Sample Contact Management Formulas
Use these formulas to manage contact details
For details about using the functions included in these samples see Formula Operators and Functions on
page
Contacts Account Discount Percent
This percent formula displays the accounts Discount Percent field on the contacts page
AccountDiscountPercentc
Contacts Account Name
This formula displays the standard Account Name field on the contacts page
AccountName
Examples of Advanced Formula Fields
Sample Contact Management Formulas
Contacts Account Phone
This formula displays the standard Account Phone field on the contacts page
AccountPhone
Contacts Account Rating
Use this formula to display the Account Rating field on the contacts page
CASEAccountRating Hot Hot Warm Warm Cold Cold
Not Rated
Contacts Account Website
This formula displays the standard Account Website field on the contacts page
AccountWebsite
If the account website URL is long use the HYPERLINK function to display a label such as Click Here
instead of the URL For example
IFAccountWebsite
IF
ORLEFTAccountWebsite httpLEFTAccountWebsite
https
HYPERLINK AccountWebsite Click Here
HYPERLINK https AccountWebsite Click Here
This formula also adds the necessary https before a URL if http or https wasnt previously
included in the URL field
Contacts LinkedIn Profile
You can configure a link that appears on your contacts profile page that sends you to their LinkedIn profile
To do so
From the object management settings for contacts go to Buttons Links and Actions
Click New Button or Link
Enter a Label for this link like LinkedInLink
Enter this formula in the content box
httpswwwlinkedincomsearchfpsearchtypepeoplekeywords
ContactFirstNameContactLastName
Click Save
Remember to add this link to the Contact page layout in order for it to show up
Examples of Advanced Formula Fields
Sample Contact Management Formulas
Contact Identification Numbering
This formula displays the first five characters of a name and the last four characters of a social security
number separated by a dash This example uses a text custom field called SSN
TRIMLEFTLastName
TRIMRIGHTSSNc
Contact Preferred Phone
This formula displays the contacts preferred contact method in a contact related listwork phone home
phone or mobile phonebased on a selected option in a Preferred Phone custom picklist
CASEPreferredPhonec
Work w Phone
Home h HomePhone
Mobile m MobilePhone
No Preferred Phone
Contact Priority
This formula assesses the importance of a contact based on the account rating and the contacts title If
the account rating is Hot or the title starts with Executive then the priority is high P If the account
rating is Warm or the title starts with VP then the priority is medium P and if the account rating is
Cold then the priority is low P
IFORISPICKVALAccountRating Hot CONTAINSTitle Executive
P
IFORISPICKVALAccountRating Warm CONTAINSTitle VP P
IFISPICKVALAccountRating Cold P
P
Contact Yahoo ID
This formula displays a clickable Yahoo Messenger icon indicating if the person is logged on to the service
Users can click the icon to launch a Yahoo Messenger conversation with the person This example uses
a custom text field called Yahoo Name on contacts where you can store the contacts Yahoo Messenger
ID
HYPERLINKymsgrsendIM YahooNamec
IMAGEhttpsopiyahoocomonlineu
YahooNamec
mgt Yahoo
Examples of Advanced Formula Fields
Sample Data Categorization Formulas
Dynamic Address Formatting
This formula field displays a formatted mailing address for a contact in standard format including spaces
and line breaks where appropriate depending on the country
CASEShippingCountry
USA
ShippingStreet BR
ShippingCity
ShippingState
ShippingPostalCode BR
ShippingCountry
France
ShippingStreet BR
ShippingPostalCode
ShippingCity BR
ShippingCountry etc
Phone Country Code
This formula determines the phone country code of a contact based on the Mailing Country of
the mailing address
CASEMailingCountry
USA
Canada
France
UK
Australia
Japan
Unformatted Phone Number
This formula removes the parentheses and dash characters from North American phone numbers This
formula is necessary for some autodialer software
IFCountryCodec MID Phone MIDPhone
MIDPhone Phone
Sample Data Categorization Formulas
Use these formulas for data categorizations
For details about using the functions included in these samples see Formula Operators and Functions on
page
Examples of Advanced Formula Fields
Using Date DateTime and Time Values in Formulas
Deal Size Large and Small
This formula displays Large Deal for deals over one million dollars or Small Deal for deals under one
million dollars
IFSalesPricec
Large Deal
Small Deal
Deal Size Small
This formula displays Small if the price and quantity are less than one This field is blank if the asset has
a price or quantity greater than one
IFANDPriceQuantitySmall null
Product Categorization
This formula checks the content of a custom text field named ProductType and returns Parts
for any product with the word part in it Otherwise it returns Service The values are casesensitive
so if a ProductType field contains the text Part or PART this formula returns Services
IFCONTAINSProductTypec part Parts Service
Using Date DateTime and Time Values in Formulas
Date formulas are useful for managing payment deadlines contract ages or any other features of your
organization that are time or date dependent
Two data types are used for working with dates Date and DateTime One data type Time is independent
of the date for tracking time such as business hours Most values that are used when working with dates
are of the Date data type which store the year month and day Some fields such as CreatedDate are
DateTime fields meaning they not only store a date value but also a time value stored in GMT but
displayed in the users time zone Date DateTime and Time fields are formatted in the users locale when
viewed in reports and record detail pages A Time values precision is in milliseconds A DateTime values
precision is in seconds
You can use operations like addition and subtraction on Date DateTime and TIme values to calculate a
future date or elapsed time between two dates or times If you subtract one date from another for example
the resulting value will be the difference between the two initial values in days Number data type The
same operation between two DateTime values returns a decimal value indicating the difference in number
of days hours and minutes The same operation between two Time values returns millisecond
For example if the difference between two DateTime values is that means the two values are
separated by five days hours of a day and minutes of a day You can also add numeric
values to Dates and DateTimes For example the operation TODAY returns three days after
todays date For more information and examples of working with dates see the list of Sample Date
Formulas
Throughout the examples the variables date and datetime are used in place of actual Date and
DateTime fields or values
Examples of Advanced Formula Fields
Using Date DateTime and Time Values in Formulas
Keep in mind that complex date functions tend to compile to a larger size than text or number formula
functions so you might run into issues with formula compile size See Tips for Reducing Formula Size for
help with this problem
TODAY NOW and TIMENOW
The TODAY function returns the current day month and year as a Date data type This function is
useful for formulas where you are concerned with how many days have passed since a previous date the
date of a certain number of days in the future or if you just want to display the current date
The NOW function returns the DateTime value of the current moment Its useful when you are
concerned with specific times of day as well as the date
The TIMENOW function returns a value in GMT representing the current time without the date Use
this function instead of the NOW function if you want the current hour minute seconds or milliseconds
This value is useful for tracking time like work shifts or elapsed time
For details on how to convert between Date values and DateTime values see Converting Between
DateTime and Date on page
The DATE Function
The DATE function returns a Date value given a year month and day Numerical YMD values and
the YEAR MONTH and DAY functions are valid parameters for DATE For example DATE
returns June Similarly DATE YEAR TODAY MONTH
TODAY returns the Date value of the first day three months from today in the current
year assuming the date is valid for example the month falls between and
If the inputted YMD values result in an invalid date the DATE function returns an error so error
checking is an important part of working with Date values You can read about methods for handling
invalid dates in Sample Date Formulas
Converting Between DateTime and Date
Date and DateTime arent interchangeable data types so when you want to perform operations between
Date and DateTime values you need to convert the values so they are both the same type Some functions
such as YEAR MONTH and DAY also only work on Date values so DateTime values must
be converted first
Use the DATEVALUE datetime function to return the Date value of a DateTime For example
to get the year from a DateTime use YEAR DATEVALUE datetime
You can convert a Date value to a DateTime using the DATETIMEVALUE date function The
time will be set to am in Greenwich Mean Time GMT and then converted to the time zone of the
user viewing the record when its displayed For a user located in San Francisco DATETIMEVALUE
TODAY returns pm on the previous day during Daylight Saving Time rather than am
of the current day See A Note About DateTime and Time Zones on page for more information
Converting Between DateTime and Time
The TIMEVALUE function returns a Time data type value in HHMMSSMS
hoursminutessecondsmilliseconds format using a hour clock Numerical HMSMS values and the
Examples of Advanced Formula Fields
Using Date DateTime and Time Values in Formulas
HOUR MINUTE SECONDS and MILLISECONDS functions are valid parameters for
TIMEVALUE
Use the TIMEVALUEvalue function to return the Time value of a DateTime type text merge field
or expression For example extract the time from a ClosedDate DateTime value with
TIMEVALUEClosedDate
Converting Between Date and Text
If you want to include a date as part of a string wrap the Date value in the TEXT function to convert
it to text For example if you want to return todays date as text use
Todays date is TEXT TODAY
This returns the date in the format YYYYMMDD rather than in the localedependent format You can
change the format by extracting the day month and year from the date first and then recombining them
in the format you want For example
Todays date is TEXT MONTH date TEXT DAY date
TEXT YEAR date
You can also convert text to a Date so you can use the string value with your other Date fields and formulas
Youll want your text to be formatted as YYYYMMDD Use this formula to return the Date value
DATEVALUE YYYYMMDD
Converting Between DateTime and Text
You can include DateTime values in a string using the TEXT function but you need to be careful of
time zones For example consider this formula
The current date and time is TEXT NOW
In this formula NOW is offset to GMT Normally NOW would be converted to the users time zone
when viewed but because its been converted to text the conversion wont happen So if you execute
this formula on August st at PM in San Francisco time GMT the result is The current date and
time is Z
When you convert a DateTime to text a Z is included at the end to indicate GMT TEXT datetime
returns Z if the field is blank So if the DateTime value youre working with might be blank check for
this before converting to text
IF
ISBLANK datetime
TEXT datetime
To convert a string to a DateTime value use DATETIMEVALUE passing in a string in the format
YYYYMMDD HHMMSS This method returns the DateTime value in GMT
Examples of Advanced Formula Fields
Using Date DateTime and Time Values in Formulas
Converting Between Time and Text
If you want to include time as part of a string wrap the Time value in the TEXT function to convert
it to text For example if you want to return the current time as text use
The time is TEXT TIMENOW
This function returns the time in the format HHMMSSMS
You can also convert text to a Time data type so you can use the string value with your other Time fields
and formulas Format your text as HHMMSSMS on a hour clock Use the TIMEVALUE function
TIMEVALUE
A Note About DateTime and Time Zones
Date and DateTime values are stored in GMT When a record is saved field values are adjusted from the
users time zone to GMT and then adjusted back to the viewers time zone when displayed in record detail
pages and reports With Date conversions this doesnt pose a problem since converting a DateTime to
a Date results in the same Date value
When working with DateTime fields and values however the conversion is always done in GMT not the
users time zone Subtracting a standard DateTime field from another isnt a problem because both fields
are in the same time zone When one of the values in the calculation is a conversion from a Text or Date
value to a DateTime value however the results are different
Lets say a San Francisco user enters a value of AM on August in a custom DateTime field
called DateTimec This value is stored as Z because the time difference in
Pacific Daylight Time is GMT At pm PDT on August st the user views the record and the following
formula is run
DateTimec NOW
In the calculation NOW is Z and then subtracted from Z
to return the expected result of hours
Suppose that instead of NOW the formula converts the string to a DateTime
value
DateTimec DATETIMEVALUE
In this case DATETIMEVALUE is Z and
returns a result of or hours
Theres no way to determine a users time zone in a formula If all of your users are in the same time zone
you can adjust the time zone difference by adding or subtracting the time difference between the users
time zone and GMT to your converted values However since time zones can be affected by Daylight
Saving Time and the start and end dates for DST are different each year this is difficult to manage in a
formula We recommend using Apex for transactions that require converting between DateTime values
and Text or Date values
Sample Date Formulas
Use the sample formulas in this topic to manipulate and perform calculations with date and time
Examples of Advanced Formula Fields
Using Date DateTime and Time Values in Formulas
Find the Day Month or Year from a Date
Use the functions DAY date MONTH date and YEAR date to return their numerical
values Replace date with a value of type Date for example TODAY
To use these functions with DateTime values first convert them to a date with the DATEVALUE
function For example DAY DATEVALUE datetime
Find Out If a Year Is a Leap Year
This formula determines whether a year is a leap year A year is only a leap year if its divisible by or
if its divisible by four but not by
OR
MOD YEAR date
AND
MOD YEAR date
MOD YEAR date
Find Which Quarter a Date Is In
For standard quarters you can determine which quarter a date falls in using this formula This formula
returns the number of the quarter that date falls in by dividing the current month by three the
number of months in each quarter and taking the ceiling
CEILING MONTH date
The formula for shifted quarters is similar but shifts the month of the date by the number of months
between January and the first quarter of the fiscal year The following example shows how to find a dates
quarter if Q starts in February instead of January
CEILING MONTH date
ITo check whether a date is in the current quarter add a check to compare the dates year and quarter
with TODAYs year and quarter
AND
CEILING MONTH date CEILING MONTH TODAY
YEAR date YEAR TODAY
Find the Week of the Year a Date Is In
To find the number of a dates week of the year use this formula
IF
CEILING date DATE YEAR date
CEILING date DATE YEAR date
Examples of Advanced Formula Fields
Using Date DateTime and Time Values in Formulas
To find the current week number determine the days to date in the current year and divide that value by
The IF statement ensures that the week number the formula returns doesnt exceed So if the
given date is December of the given year the formula returns even though its more than weeks
after the first week of January
Find Whether Two Dates Are in the Same Month
To determine whether two Dates fall in the same month say for a validation rule to determine whether
an opportunity Close Date is in the current month use this formula
AND
MONTH date MONTH date
YEAR date YEAR date
Find the Last Day of the Month
The easiest way to find the last day of a month is to find the first day of the next month and subtract a
day
IF
MONTH date
DATE YEAR date
DATE YEAR date MONTH date
Display the Month as a String instead of a Number
To return the month as a text string instead of a number use
CASE
MONTH date
January
February
March
April
May
June
July
August
September
October
November
December
If your organization uses multiple languages you can replace the names of the month with a custom
label
CASE
MONTH date
LabelMonthofYear
LabelMonthofYear
Examples of Advanced Formula Fields
Using Date DateTime and Time Values in Formulas
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
Find and Display the Day of the Week from a Date
To find the day of the week from a Date value use a known Sunday for example January and
subtract it from the date for example TODAY to get the difference in days The MOD function
finds the remainder of this result when divided by to give the numerical value of the day of the week
between Sunday and Saturday This formula finds the result and then returns the text name of that
day
CASE
MOD date DATE
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
This formula only works for dates after If you work with older dates use the same process
with any Sunday before to your earliest date for example
You can adjust this formula if your week starts on a different day For example if your week starts on
Monday you can use January in your condition The new formula looks like this
CASE
MOD date DATE
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday
To get the formula for the name of the month if your organization uses multiple languages you can
replace the names of the day of the week with a variable like LabelDayofWeek and so on
