in implementation guides ~ read.
Salesforce Useful Formula Fields

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.

Download

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

httpwwwbbccouksearchnewsqName

BBC News

Bloomberg News Search
This formula creates a link to an accounts ticker symbol on the Bloomberg website

httpwwwbloombergcommarketssymbolsearchqueryTickerSymbol
Bloomberg News

CNN News Search

This formula creates a link to a CNN news search site using the Account Name

httphttpwwwcnncomsearchqueryName

CNN News

MarketWatch Search
This formula creates a link to an accounts ticker symbol on the Marketwatchcom website

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

httpwwwgooglecomqName
Google

Google News Search
This formula creates a link to a Google news search site using the Account Name

httpnewsgooglecomnewssearchenqName
Google News

Yahoo Search
This formula creates a link to a Yahoo search site using the Account Name

httpsearchyahoocomsearchpName
Yahoo Search

Yahoo News Search
This formula creates a link to a Yahoo news search site using the Account Name

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

***