# 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