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 on page

Sample Account Management Formulas
For details about using the functions included in these samples see Formula Operators and Functions 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 February

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 is not Activated this field is blank
IFISPICKVALContractStatusc Activated

NOW ContractActivatedDatec null

Sample Account Media Service Formulas
For details about using the functions included in these samples see Formula Operators and Functions 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
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
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 will be the amount times for any closedwon opportunity Open or lost opportunities
will 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 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
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

Contacts Account Phone
This formula displays the standard Account Phone field on the contacts page
AccountPhone

Examples of Advanced Formula Fields

Sample Contact Management Formulas

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 http AccountWebsite Click Here

This formula also adds the necessary http or https before a URL if neither were 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
httpwwwlinkedincomsearchfpsearchtypepeoplekeywords
ContactFirstNameContactLastName

Click Save
Remember to add this link to the Contact page layout in order for it to show up

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 Note that this example uses a text custom field called SSN

TRIMLEFTLastName
TRIMRIGHTSSNc

Examples of Advanced Formula Fields

Sample Contact Management Formulas

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

IMAGEhttpopiyahoocomonlineu
YahooNamec
mgt Yahoo

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

Examples of Advanced Formula Fields

Sample Data Categorization Formulas

ShippingPostalCode BR

ShippingCountry
France

ShippingStreet BR

ShippingPostalCode

ShippingCity BR

ShippingCountry etc

Telephone Country Code
This formula determines the telephone 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 is
necessary for some autodialer software

IFCountryCodec MID Phone MIDPhone

MIDPhone Phone

Sample Data Categorization Formulas
For details about using the functions included in these samples see Formula Operators and Functions on
page

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

Examples of Advanced Formula Fields

Using Date DateTime and Time Values in Formulas

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 Note that the values are case sensitive

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 are formatted in the users locale
when viewed in reports and record detail pages
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
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

Examples of Advanced Formula Fields

Using Date DateTime and Time Values in Formulas

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 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

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

Examples of Advanced Formula Fields

Using Date DateTime and Time Values in Formulas

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

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

Examples of Advanced Formula Fields

Using Date DateTime and Time Values in Formulas

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
Finding the Day Month or Year from a Date

Use the functions DAY date MONTH date and YEAR date to return their respective

numerical values Replace date with a value of type Date eg TODAY

To use these functions with DateTime values first convert them to a date with the DATEVALUE

function For example DAY DATEVALUE datetime

Finding Out if a Year Is a Leap Year

This formula determines whether or not 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

Examples of Advanced Formula Fields

Using Date DateTime and Time Values in Formulas

Finding 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 in which date falls 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 example below illustrates how you can find

a dates quarter if Q starts in February instead of January

CEILING MONTH date

If you want to 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

Finding 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

You can find the current week by determining how many days there have been in the current year and

dividing 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 week of January

Finding 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

Finding 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

Examples of Advanced Formula Fields

Using Date DateTime and Time Values in Formulas

DATE YEAR date

DATE YEAR date MONTH date

Displaying 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
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear

Finding and Displaying the Day of the Week From a Date
To find the day of the week from a Date value use a known Sunday eg January and subtract it

from the date eg 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 The formula below finds the result and then returns the text name of that day

CASE

MOD date DATE

Sunday

Examples of Advanced Formula Fields

Using Date DateTime and Time Values in Formulas

Monday
Tuesday
Wednesday
Thursday
Friday
Saturday

Note that this formula only works for dates after If youre working with older dates use the
same process with any Sunday prior to your earliest date eg
You can also 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

Like the formula for getting 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 etc

Finding the Next Day of the Week After a Date
To find the date of the next occurrence of a particular day of the week following a given Date get the
difference in the number of days of the week between a date and a dayofweek a number
where Sunday and Saturday By adding this difference to the current date youll find the date of

the dayofweek The IF statement in this formula handles cases where the dayofweek

is prior to the day of the week of the date value eg date is a Thursday and dayofweek is a
Monday by adding to the difference

date dayofweek MOD date DATE

IF

MOD date DATE dayofweek

You can substitute either a constant or another field in for the dayofweek value based on your
needs

Finding the Number of Days Between Two Dates
To find the number of days between two dates date and date subtract the earlier date from
the later date date date

***