Examples of Advanced Formula Fields

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

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

Account Rating

This formula evaluates Annual Revenue, Billing Country, and Type, and assigns a value

of “Hot,” “Warm,” or “Cold.”

IF (AND (AnnualRevenue > 10000000,

CONTAINS (CASE (BillingCountry, "United States", "US", "America", "US",

"USA", "US", "NA"), "US")),

IF(ISPICKVAL(Type, "Manufacturing Partner"), "Hot",

IF(OR (ISPICKVAL (Type, "Channel Partner/Reseller"),

ISPICKVAL(Type, "Installation Partner")), "Warm", "Cold")),

"Cold")

In addition, you can reference this Account Rating formula field from the contact object using cross-object

formulas.

Account.Account_Rating__c

Account Region

This formula returns a text value of “North,” “South,” “East,” “West,” or “Central” based on the Billing

State/Province of the account.

IF(ISBLANK(BillingState), "None",

IF(CONTAINS("AK:AZ:CA:HA:NV:NM:OR:UT:WA", BillingState), "West",

IF(CONTAINS("CO:ID:MT:KS:OK:TX:WY", BillingState), "Central",

IF(CONTAINS("CT:ME:MA:NH:NY:PA:RI:VT", BillingState), "East",

IF(CONTAINS("AL:AR:DC:DE:FL:GA:KY:LA:MD:MS:NC:NJ:SC:TN:VA:WV",

BillingState), "South",

IF(CONTAINS("IL:IN:IA:MI:MN:MO:NE:ND:OH:SD:WI", BillingState), "North",

"Other"))))))

Last updated: July 5, 2024

Contract Aging

This formula calculates the number of days since a contract with an account was activated. If the contract

Status isn’t “Activated,” this field is blank.

IF(ISPICKVAL(Contract_Status__c, "Activated"),

NOW() - Contract_Activated_Date__c, 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 32.

BBC

™

News Search

This formula creates a link to a BBC news search site based on the Account Name.

HYPERLINK(

"http://www.bbc.co.uk/search/news/?q="&Name,

"BBC News")

Bloomberg

™

News Search

This formula creates a link to an account's ticker symbol on the Bloomberg website.

HYPERLINK(

"http://www.bloomberg.com/markets/symbolsearch?query="&TickerSymbol,

"Bloomberg News")

CNN

™

News Search

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

HYPERLINK(

"http://http://www.cnn.com/search/?query="&Name,

"CNN News")

MarketWatch

™

This formula creates a link to an account's ticker symbol on the Marketwatch.com website.

HYPERLINK(

"http://www.marketwatch.com/investing/stock/"&TickerSymbol,

"Marketwatch")

Sample Account Media Service FormulasExamples of Advanced Formula Fields

Google

™

This formula creates a link to a Google search site using the Account Name.

HYPERLINK(

"http://www.google.com/#q="&Name,

"Google")

Google News Search

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

HYPERLINK(

"http://news.google.com/news/search?en&q="&Name,

"Google News")

Yahoo!

™

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

HYPERLINK(

"http://search.yahoo.com/search?p="&Name,

"Yahoo Search")

Yahoo! News Search

This formula creates a link to a Yahoo! news search site using the Account Name.

HYPERLINK(

"http://news.search.yahoo.com/search/news?p="&Name,

"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 32.

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.

HYPERLINK("http://servername/call?id=" & Id & "&phone=" &

Phone, Phone)

Sample Case Management FormulasExamples of Advanced Formula Fields

Case Categorization

This formula displays a text value of “RED,” “YELLOW,” or “GREEN,” depending on the value of a case age

custom text field.

IF(DaysOpen__c > 20, "RED",

IF(DaysOpen__c > 10, "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 “0.” The formula returns a value of “1” for each field that contains a value

and multiplies this total by fifty to give you the percentage of fields that contain data.

(IF(ISBLANK(Problem_Num__c), 0, 1) + IF(ISBLANK(Severity_Num__c ),

0,1)) * 50

Suggested Agent Prompts

This formula prompts an agent with cross-sell offers based on past purchases.

CASE(Product_Purch__c,

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

CASE(Problem__c,

"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 32.

Commission Amounts for Opportunities

The following is a simple formula where commission is based on a flat 2% of the opportunity Amount.

IF(ISPICKVAL(StageName, "Closed Won"),

ROUND(Amount *0.02, 2), 0)

Sample Commission Calculations FormulasExamples of Advanced Formula Fields

This example calculates the commission amount for any opportunity that has a “Closed Won” stage. The

value of this field is the amount times 0.02 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 9% commission rate for deals

over 100,000 and an 8% commission rate for smaller deals.

IF(Amount > 100000, 0.09, 0.08 )

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.

IF(Commission__c >=

1000000, "YES", "NO")

Commission Maximum

This formula determines what commission to log for an asset based on which is greater: the user's

commission percentage of the price, the price times the discount percent stored for the account or 100

dollars. This example assumes you have two custom percent fields on users and assets.

MAX($User.Commission_Percent__c * Price,

Price * Account_Discount__c, 100)

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

Contact's Account Discount Percent

This percent formula displays the account's Discount Percent field on the contacts page.

Account.Discount_Percent__c

Contact's Account Name

This formula displays the standard Account Name field on the contacts page.

Account.Name

Sample Contact Management FormulasExamples of Advanced Formula Fields

Contact's Account Phone

This formula displays the standard Account Phone field on the contacts page.

Account.Phone

Contact's Account Rating

Use this formula to display the Account Rating field on the contacts page.

CASE(Account.Rating, "Hot", "Hot", "Warm", "Warm", "Cold", "Cold",

"Not Rated")

Contact's Account Website

This formula displays the standard Account Website field on the contacts page.

Account.Website

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:

IF(Account.Website="", "",

IF(

OR(LEFT(Account.Website, 7) = "http://",LEFT(Account.Website, 8) =

"https://"),

HYPERLINK( Account.Website , "Click Here" ),

HYPERLINK( "https://" & Account.Website , "Click Here" )

)

This formula also adds the necessary "https://" before a URL if “http://” or “https://” wasn’t previously

included in the URL field.

Contact's LinkedIn

™

Profile

You can configure a link that appears on your contacts' profile page that sends you to their LinkedIn profile.

To do so:

1. From the object management settings for contacts, go to Buttons, Links, and Actions.

2. Click New Button or Link.

3. Enter a Label for this link, like LinkedInLink.

4. Enter this formula in the content box:

https://www.linkedin.com/search/fpsearch?type=people&keywords

={!Contact.FirstName}+{!Contact.LastName}

5. Click Save.

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

Sample Contact Management FormulasExamples of Advanced Formula Fields

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.

TRIM(LEFT(LastName, 5)) &

"-" & TRIM(RIGHT(SSN__c, 4))

Contact Preferred Phone

This formula displays the contact’s preferred contact method in a contact related list—work phone, home

phone, or mobile phone—based on a selected option in a Preferred Phone custom picklist.

CASE(Preferred_Phone__c,

"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 contact's title. If

the account rating is Hot or the title starts with Executive, then the priority is high (P1). If the account

rating is Warm or the title starts with VP then the priority is medium (P2), and if the account rating is

Cold then the priority is low (P3).

IF(OR(ISPICKVAL(Account.Rating, "Hot"), CONTAINS(Title, "Executive")),

"P1",

IF(OR(ISPICKVAL(Account.Rating, "Warm"), CONTAINS(Title, "VP")), "P2",

IF(ISPICKVAL(Account.Rating, "Cold"), "P3",

"P3")

)

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 contact's Yahoo! Messenger

ID.

HYPERLINK("ymsgr:sendIM?" & Yahoo_Name__c,

IMAGE("https://opi.yahoo.com/online?u=" &

Yahoo_Name__c &

"&m;=g&t;=0", "Yahoo"))

Sample Contact Management FormulasExamples of Advanced Formula Fields

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.

CASE(ShippingCountry,

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

CASE(MailingCountry,

"USA", "1",

"Canada", "1",

"France", "33",

"UK", "44",

"Australia", "61",

"Japan", "81",

"?")

Unformatted Phone Number

This formula removes the parentheses and dash characters from North American phone numbers. This

formula is necessary for some auto-dialer software.

IF(Country_Code__c = "1", MID( Phone ,2, 3) & MID(Phone,7,3) &

MID(Phone,11,4), 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 32.

Sample Data Categorization FormulasExamples of Advanced Formula Fields

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.

IF(Sales_Price__c > 1000000,

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

IF(AND(Price<1,Quantity<1),"Small", null)

Product Categorization

This formula checks the content of a custom text field named Product_Type and returns Parts

for any product with the word “part” in it. Otherwise, it returns Service. The values are case-sensitive,

so if a Product_Type field contains the text “Part” or “PART,” this formula returns Services.

IF(CONTAINS(Product_Type__c, "part"), "Parts", "Service")

Using Date, Date/Time, 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 Date/Time. 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

Date/Time 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, Date/Time, and Time fields are formatted in the user’s locale when

viewed in reports and record detail pages. A Time value’s precision is in milliseconds. A Date/Time value’s

precision is in seconds.

You can use operations like addition and subtraction on Date, Date/Time, 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 Date/Time 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 Date/Time values is 5.52, that means the two values are

separated by five days, 12 hours (0.5 of a day), and 28 minutes (0.02 of a day). You can also add numeric

values to Dates and Date/Times. For example, the operation TODAY() + 3 returns three days after

today’s date. For more information and examples of working with dates, see the list of Sample Date

Formulas.

Throughout the examples, the variables date and date/time are used in place of actual Date and

Date/Time fields or values.

Using Date, Date/Time, and Time Values in FormulasExamples of Advanced Formula Fields

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 Date/Time value of the current moment. It’s 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 Date/Time values, see Converting Between

Date/Time and Date on page 10.

The DATE() Function

The DATE() function returns a Date value, given a year, month, and day. Numerical Y/M/D values and

the YEAR(), MONTH(), and DAY() functions are valid parameters for DATE(). For example DATE(

2013, 6, 1 ) returns June 1, 2013. Similarly, DATE( YEAR( TODAY() ), MONTH(

TODAY() ) + 3, 1) 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 1 and 12).

If the inputted Y/M/D 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 Date/Time and Date

Date and Date/Time aren’t interchangeable data types, so when you want to perform operations between

Date and Date/Time 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 Date/Time values must

be converted first.

Use the DATEVALUE( date/time ) function to return the Date value of a Date/Time. For example,

to get the year from a Date/Time, use YEAR( DATEVALUE( date/time ) ) ).

You can convert a Date value to a Date/Time using the DATETIMEVALUE( date ) function. The

time will be set to 12:00 a.m. in Greenwich Mean Time (GMT), and then converted to the time zone of the

user viewing the record when it’s displayed. For a user located in San Francisco, DATETIMEVALUE(

TODAY() ) returns 5:00 p.m. on the previous day (during Daylight Saving Time) rather than 12:00 a.m.

of the current day. See A Note About Date/Time and Time Zones on page 12 for more information.

Converting Between Date/Time and Time

The TIMEVALUE() function returns a Time data type value in “HH:MM:SS.MS”

(hours:minutes:seconds.milliseconds) format using a 24-hour clock. Numerical H/M/S/MS values and the

Using Date, Date/Time, and Time Values in FormulasExamples of Advanced Formula Fields

HOUR(), MINUTE(), SECONDS(), and MILLISECONDS() functions are valid parameters for

TIMEVALUE().

Use the TIMEVALUE(value) function to return the Time value of a Date/Time type, text, merge field

or expression. For example, extract the time from a ClosedDate Date/Time value with

TIMEVALUE(ClosedDate).

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 today’s date as text, use:

"Today's date is " & TEXT( TODAY() )

This returns the date in the format “YYYY-MM-DD” rather than in the locale-dependent 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:

"Today's 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.

You’ll want your text to be formatted as “YYYY-MM-DD”. Use this formula to return the Date value:

DATEVALUE( "YYYY-MM-DD" )

Converting Between Date/Time and Text

You can include Date/Time 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 user’s time zone

when viewed, but because it’s been converted to text, the conversion won’t happen. So if you execute

this formula on August 1st at 5:00 PM in San Francisco time (GMT-7), the result is “The current date and

time is 2013–08–02 00:00:00Z”.

When you convert a Date/Time to text, a “Z” is included at the end to indicate GMT. TEXT( date/time

) returns “Z” if the field is blank. So if the Date/Time value you’re working with might be blank, check for

this before converting to text:

IF(

ISBLANK( date/time ),

"",

TEXT( date/time )

)

To convert a string to a Date/Time value, use DATETIMEVALUE() passing in a string in the format

“YYYY-MM-DD HH:MM:SS”. This method returns the Date/Time value in GMT.

Using Date, Date/Time, and Time Values in FormulasExamples of Advanced Formula Fields

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 “HH:MM:SS.MS”.

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 “HH:MM:SS.MS” on a 24-hour clock. Use the TIMEVALUE() function:

TIMEVALUE("17:30:45.125")

A Note About Date/Time and Time Zones

Date and Date/Time values are stored in GMT. When a record is saved, field values are adjusted from the

user’s time zone to GMT, and then adjusted back to the viewer’s time zone when displayed in record detail

pages and reports. With Date conversions this doesn't pose a problem, since converting a Date/Time to

a Date results in the same Date value.

When working with Date/Time fields and values, however, the conversion is always done in GMT, not the

user’s time zone. Subtracting a standard Date/Time field from another isn’t 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 Date/Time value, however, the results are different.

Let’s say a San Francisco user enters a value of 12:00 AM on August 2, 2013 in a custom Date/Time field

called Date_Time_c. This value is stored as 2013–08–02 07:00:00Z, because the time difference in

Pacific Daylight Time is GMT-7. At 12:00 p.m. PDT on August 1st, the user views the record and the following

formula is run:

Date_Time_c - NOW()

In the calculation, NOW() is 2013–08–01 19:00:00Z, and then subtracted from 2013–08–02 07:00:00Z,

to return the expected result of 0.5 (12 hours).

Suppose that instead of NOW(), the formula converts the string “2013–08–01 12:00:00” to a Date/Time

value:

Date_Time_c - DATETIMEVALUE( "2013-08-01 12:00:00" )

In this case, DATETIMEVALUE( “2013–08–01 12:00:00” ) is 2013–08–01 12:00:00Z, and

returns a result of 0.79167, or 19 hours.

There’s no way to determine a user’s 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 Date/Time 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.

Using Date, Date/Time, and Time Values in FormulasExamples of Advanced Formula Fields

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 Date/Time values, first convert them to a date with the DATEVALUE()

function. For example, DAY( DATEVALUE( date/time )).

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 it’s divisible by 400, or

if it’s divisible by four but not by 100.

OR(

MOD( YEAR( date ), 400 ) = 0,

AND(

MOD( YEAR( date ), 4 ) = 0,

MOD( YEAR( date ), 100 ) != 0

)

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 (1–4) by dividing the current month by three (the

number of months in each quarter) and taking the ceiling.

CEILING( MONTH ( date ) / 3 )

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 date’s

quarter if Q1 starts in February instead of January.

CEILING( ( MONTH ( date ) - 1 ) / 3)

ITo check whether a date is in the current quarter, add a check to compare the date’s year and quarter

with TODAY()’s year and quarter.

AND(

CEILING( MONTH( date ) / 3 ) = CEILING( MONTH( TODAY() ) / 3 ),

YEAR( date ) = YEAR( TODAY() )

)

Find the Week of the Year a Date Is In

To find the number of a date’s week of the year, use this formula:

IF(

CEILING( ( date - DATE( YEAR( date ), 1, 1) + 1) / 7) > 52,

52,

CEILING( ( date - DATE( YEAR( date ), 1, 1) + 1) / 7)

)

Using Date, Date/Time, and Time Values in FormulasExamples of Advanced Formula Fields

To find the current week number, determine the days to date in the current year and divide that value by

7. The IF() statement ensures that the week number the formula returns doesn’t exceed 52. So if the

given date is December 31 of the given year, the formula returns 52, even though it’s more than 52 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_1 ) == MONTH( date_2 ),

YEAR( date_1 ) == YEAR( date_2 )

)

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 ) = 12,

DATE( YEAR( date ), 12, 31 ),

DATE( YEAR( date ), MONTH ( date ) + 1, 1 ) - 1

)

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

1, "January",

2, "February",

3, "March",

4, "April",

5, "May",

6, "June",

7, "July",

8, "August",

9, "September",

10, "October",

11, "November",

"December"

)

If your organization uses multiple languages, you can replace the names of the month with a custom

label:

CASE(

MONTH( date ),

1, $Label.Month_of_Year_1,

2, $Label.Month_of_Year_2,

Using Date, Date/Time, and Time Values in FormulasExamples of Advanced Formula Fields

3, $Label.Month_of_Year_3,

4, $Label.Month_of_Year_4,

5, $Label.Month_of_Year_5,

6, $Label.Month_of_Year_6,

7, $Label.Month_of_Year_7,

8, $Label.Month_of_Year_8,

9, $Label.Month_of_Year_9,

10, $Label.Month_of_Year_10,

11, $Label.Month_of_Year_11,

$Label.Month_of_Year_12

)

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 7, 1900, 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 7 to give the numerical value of the day of the week

between 0 (Sunday) and 6 (Saturday). This formula finds the result and then returns the text name of that

day.

CASE(

MOD( date - DATE( 1900, 1, 7 ), 7 ),

0, "Sunday",

1, "Monday",

2, "Tuesday",

3, "Wednesday",

4, "Thursday",

5, "Friday",

"Saturday"

)

This formula only works for dates after 01/07/1900. If you work with older dates, use the same process

with any Sunday before to your earliest date, for example, 01/05/1800.

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 8, 1900 in your condition. The new formula looks like this:

CASE(

MOD( date - DATE( 1900, 1, 8 ), 7 ),

0, "Monday",

1, "Tuesday",

2, "Wednesday",

3, "Thursday",

4, "Friday",

5, "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 $Label.Day_of_Week_1, and so on.

Using Date, Date/Time, and Time Values in FormulasExamples of Advanced Formula Fields

Find 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 day_of_week, a number 0–6

where 0 = Sunday and 6 = Saturday. By adding this difference to the current date, you can find the date

of the day_of_week. The IF() statement in this formula handles cases where the day_of_week

is before the day of the week of the date value (for example date is a Thursday and day_of_week

is a Monday) by adding 7 to the difference.

date + ( day_of_week - MOD( date - DATE( 1900, 1, 7 ), 7 ) )

IF(

MOD( date - DATE( 1900, 1, 7 ), 7 ) >= day_of_week,

)

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

needs.

Find the Number of Days Between Two Dates

To find the number of days between two dates, date_1, and date_2, subtract the earlier date from

the later date: date_1 — date_2

You can alter this formula slightly if you want to determine a date that’s a certain number of days in the

past. For example, to create a formula to return true if some date field is more than 30 days before the

current date and false otherwise, use a formula such as the following:

TODAY() - 30 > date

Find the Number of Weekdays Between Two Dates

Calculating how many weekdays passed between two dates is slightly more complex than calculating

total elapsed days. In this example, weekdays are Monday through Friday. The basic strategy is to choose

a reference Monday from the past and find out how many full weeks and any additional portion of a week

have passed between the reference date and your date. These values are multiplied by five for a five-day

work week, and then the difference between them is taken to calculate weekdays.

(5 * ( FLOOR( ( date_1 - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD(

date_1 - DATE( 1900, 1, 8), 7 ) ) )

(5 * ( FLOOR( ( date_2 - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD(

date_2 - DATE( 1900, 1, 8), 7 ) ) )

In this formula, date_1 is the more recent date and date_2 is the earlier date. If your work week runs

shorter or longer than five days, replace all fives in the formula with the length of your week.

Using Date, Date/Time, and Time Values in FormulasExamples of Advanced Formula Fields

Find the Number of Months Between Two Dates

To find the number of months between two dates, subtract the year of the earlier date from the year of

the later date and multiply the difference by 12. Next, subtract the month of the earlier date from the

month of the later date, and add that difference to the value of the first set of operations.

((YEAR(date_1) - YEAR(date_2))*12) + (MONTH(date_1) - MONTH(date_2))

Add Days, Months, and Years to a Date

If you want to add a certain number of days to a date, add that number to the date directly. For example,

to add 5 days to a date, the formula is date + 5.

If you want to add a certain number of months to a date, use this function.

ADDMONTHS()

For example, if you want to add 4 months to a date, use this formula.

ADDMONTHS(date + 4)

If the date that you provide is the last of any month, this formula returns the last day of the resulting

month.

To add a certain number of years to a date, use this formula.

ADDMONTHS(date, 12*num_years)

If the date that you provide is February 29, and the resulting year isn’t a leap year, the formula returns the

date as February 28. In this scenario, if you want the resulting date as March 1, use this formula.

IF( MOD((Year (ADDMONTHS(date, 12* num_years)-1960),4)=0,

ADDMONTHS(date,12* num_years)+1,ADDMONTHS(date, 12*num_years))

Add Business Days to a Date

This formula finds three business days from a given date.

CASE(

MOD( date - DATE( 1900, 1, 7 ), 7 ),

3, date + 2 + 3,

4, date + 2 + 3,

5, date + 2 + 3,

6, date + 1 + 3,

date + 3

)

This formula finds the day of the week of the date field value. If the date is a Wednesday, Thursday, or

Friday, the formula adds five calendar days, two weekend days, three weekdays, to the date to account

for the weekend. If date is a Saturday, you need four additional calendar days. For any other day of the

week Sunday Tuesday, simply add three days. You can easily modify this formula to add more or fewer

business days. The tip for getting the day of the week is useful to use to adjust this formula.

Using Date, Date/Time, and Time Values in FormulasExamples of Advanced Formula Fields

Find the Hour, Minute, or Second from a Date/Time

To get the hour, minute, and second from a Date/Time field as a numerical value, use the following

formulas where TZoffset is the difference between the user’s time zone and GMT. For hour in 24–hour

format:

VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) )

For hour in 12–hour format:

IF(

OR(

VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) = 0,

VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) = 12

12,

VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) )

IF(

VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) < 12,

)

For minutes:

VALUE( MID( TEXT( date/time - TZoffset ), 15, 2 ) )

For seconds:

VALUE( MID( TEXT( date/time - TZoffset ), 18, 2 ) )

And, to get “AM” or “PM” as a string, use:

IF(

VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) < 12,

"AM",

"PM"

)

To return the time as a string in “HH:MM:SS A/PM” format, use the following formula:

IF(

OR(

VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) = 0,

VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) = 12

"12",

TEXT( VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) )

IF(

VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) < 12,

)

Using Date, Date/Time, and Time Values in FormulasExamples of Advanced Formula Fields

)

& ":" &

MID( TEXT( date/time - TZoffset ), 15, 2 )

& ":" &

MID( TEXT( date/time - TZoffset ), 18, 2 )

& " " &

IF(

VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) < 12,

"AM",

"PM"

)

When working with time in formula fields, always consider the time difference between your organization

and GMT. See A Note About Date/Time and Time Zones on page 12 for more information about the time

zone offset used in this formula.

Find the Elapsed Time Between Date/Times

To find the difference between two Date values as a number, subtract one from the other like so: date_1

— date_2 to return the difference in days.

Finding the elapsed time between two Date/Time values is slightly more complex. This formula converts

the difference between two Date/Time values, datetime_1 and datetime_2, to days, hours, and

minutes.

IF(

datetime_1 - datetime_2 > 0 ,

TEXT( FLOOR( datetime_1 - datetime_2 ) ) & " days "

& TEXT( FLOOR( MOD( (datetime_1 - datetime_2 ) * 24, 24 ) ) ) & "

hours "

& TEXT( ROUND( MOD( (datetime_1 - datetime_2 ) * 24 * 60, 60 ), 0

) ) & " minutes",

)

Find the Number of Business Hours Between Two Date/Times

The formula to find business hours between two Date/Time values expands on the formula to find elapsed

business days. It works on the same principle of using a reference Date/Time. In this case 1/8/1900 at

16:00 GMT, or 9:00 AM PDT, and then finding your Dates’ respective distances from that reference. The

formula rounds the value it finds to the nearest hour and assumes an 8–hour, 9:00 AM5:00 PM work day.

ROUND( 8 * (

( 5 * FLOOR( ( NOW() - DATETIMEVALUE( '1900-01-08 16:00:00') ) /

7) +

MIN(5,

FLOOR ( MOD ( NOW() - DATETIMEVALUE( '1900-01-08 16:00:00'), 7)

/ 1) +

MIN( 1, 24 / 8 * ( MOD( NOW () - DATETIMEVALUE( '1900-01-08

16:00:00' ), 1 ) ) )

)

Using Date, Date/Time, and Time Values in FormulasExamples of Advanced Formula Fields

( 5 * FLOOR( ( MQL_datetime_c - DATETIMEVALUE( '1900-01-08

16:00:00') ) / 7) +

MIN( 5,

FLOOR( MOD( MQL_datetime_c - DATETIMEVALUE( '1900-01-08

16:00:00'), 7) / 1) +

MIN( 1, 24 / 8 * ( MOD( MQL_datetime_c - DATETIMEVALUE(

'1900-01-08 16:00:00' ), 1) ) )

)

2 )

You can change the eights in the formula to account for a longer or shorter work day. If you live in a

different time zone or your work day doesn’t start at 9:00 AM, change the reference time to the start of

your work day in GMT. See A Note About Date/Time and Time Zones for more information.

Sample Discounting Formulas

Use these formulas to calculate discount amounts.

For details about using the functions included in these samples, see Formula and Operator Functions.

Maintenance and Services Discount

This formula field uses two custom currency fields: Maintenance Amount and Services

Amount. It displays “Discounted” on an opportunity if its maintenance amount and services amount

don’t equal the opportunity Amount standard field value. Otherwise, it displays "Full Price."

IF(Maintenance_Amount__c + Services_Amount__c <> Amount,

"Discounted",

"Full Price")

Opportunity Discount Amount

This formula calculates the difference of the product Amount less the Discount Amount. Discount Amount

is a custom currency field.

Amount -

Discount_Amount__c

Opportunity Discount Rounded

Use this formula to calculate the discounted amount of an opportunity rounded off to two digits. This

example is a number formula field on opportunities that uses a custom percent field called Discount

Percent.

ROUND(Amount-Amount* Discount_Percent__c,2)

Sample Discounting FormulasExamples of Advanced Formula Fields

Opportunity Discount with Approval

This formula adds a “Discount Approved” checkbox to an opportunity. It uses conditional logic to check

the value of the approval flag before calculating the commission.

IF(Discount_Approved__c, ROUND(Amount – Amount * DiscountPercent__c,

2), Amount)

Sample Employee Services Formulas

Use these formulas for employee services.

For details about using the functions included in these samples, see Formula Operators and Functions on

page 32.

Bonus Calculation

This example determines an employee's bonus amount based on the smallest of two amounts: the

employee's gross income times a bonus percent or an equally divided amount of the company's

performance amount among all employees. It assumes you have a custom number field for Number

of Employees, a custom percent field for Bonus Percent, and currency custom fields for the

employee's Gross and company's Performance.

MIN(Gross__c * Bonus_Percent__c,

Performance__c / Number_of_Employees__c)

Employee 401K

This example formula determines which amount to provide in employee 401K matching based on a

matching program of half of the employee's contribution or $250, whichever is less. It assumes you have

a custom currency field for Contribution.

MIN(250, Contribution__c /2)

Hours Worked Per Week

This formula uses a custom tab to enable time tracking of hours worked per day. It uses a formula field to

sum the hours per week.

MonHours__c + TuesHours__c + WedsHours__c + ThursHours__c + FriHours__c

Total Pay Amount

This formula determines total pay by calculating regular hours multiplied by a regular pay rate, plus

overtime hours multiplied by an overtime pay rate.

Total Pay =

IF(Total_Hours__c <= 40, Total_Hours__c * Hourly_Rate__c,

40 * Hourly_Rate__c +

(Total_Hours__c - 40) * Overtime_Rate__c)

Sample Employee Services FormulasExamples of Advanced Formula Fields

Sample Expense Tracking Formulas

Use these formulas for expense tracking.

For details about using the functions included in these samples, see Formula and Operator Functions.

Expense Identifier

This formula displays the text Expense- followed by trip name and the expense number. This field a

text formula field that uses an expense number custom field.

"Expense-" &

Trip_Name__c & "-" & ExpenseNum__c

Mileage Calculation

This formula calculates mileage expenses for visiting a customer site at 35 cents a mile.

Miles_Driven__c * 0.35

Sample Financial Calculations Formulas

Use these formulas for financial calculations.

For details about using the functions included in these samples, see Formula Operators and Functions on

page 32.

Compound Interest

This formula calculates the interest that you have after T years, compounded M times per year.

Principal__c * ( 1 + Rate__c / M ) ^ ( T * M) )

Compound Interest Continuous

This formula calculates the interest that will have accumulated after T years, if continuously compounded.

Principal__c * EXP(Rate__c * T)

Consultant Cost

This formula calculates the number of consulting days times 1200 given that this formula field is a currency

data type and consulting charges a rate of $1200 per day. Consulting Days is a custom field.

Consulting_Days__c *

1200

Sample Expense Tracking FormulasExamples of Advanced Formula Fields

Gross Margin

This formula provides a simple calculation of gross margin. In this formula example, Total Sales

and Cost of Goods Sold are custom currency fields.

Total_Sales__c - Cost_of_Goods_Sold__c

Gross Margin Percent

This formula calculates the gross margin based on a margin percent.

Margin_percent__c * Items_Sold__c * Price_item__c

Payment Due Indicator

This formula returns the date five days after the contract start date whenever Payment Due Date is blank.

Payment Due Date is a custom date field.

(BLANKVALUE(Payment_Due_Date__c, StartDate +5)

Payment Status

This formula determines if the payment due date is past and the payment status is “UNPAID.” If so, it

returns the text “PAYMENT OVERDUE” and if not, it leaves the field blank. This example uses a custom date

field called Payment Due Date and a text custom field called Payment Status on contracts.

IF(

AND(Payment_Due_Date__c < TODAY(),

ISPICKVAL(Payment_Status__c, "UNPAID")),

"PAYMENT OVERDUE",

null )

Sample Image Link Formulas

Use these formulas for image links.

For details about using the functions included in these samples, see Formula Operators and Functions by

Context on page 32.

Yahoo! Instant Messenger

™

Image

This formula displays an image that indicates whether a contact or user is logged in to Yahoo! Instant

Messenger. Clicking the image launches the Yahoo! Instant Messenger window. This formula uses a custom

text field called Yahoo Name to store the contact or user’s Yahoo! ID.

IF(ISBLANK(Yahoo_Name__c),"", HYPERLINK("ymsgr:sendIM?" &

Yahoo_Name__c,

IMAGE("http://opi.yahoo.com/online?u=" & Yahoo_Name__c & "&m=g&t=0",

" ")))

Sample Image Link FormulasExamples of Advanced Formula Fields

Flags for Case Priority

This formula displays a green, yellow, or red flag image to indicate case priority.

IMAGE(

CASE( Priority,

"Low", "/img/samples/flag_green.gif",

"Medium", "/img/samples/flag_yellow.gif",

"High", "/img/samples/flag_red.gif",

"/s.gif"),

"Priority Flag")

Color Squares for Case Age

This formula displays a 30 x 30 pixel image of a red, yellow, or green, depending on the value of a Case

Age custom number field.

IF( Case_Age__c > 20,

IMAGE("/img/samples/color_red.gif", "red", 30, 30),

IF( Case_Age__c > 10,

IMAGE("/img/samples/color_yellow.gif", "yellow", 30, 30),

IMAGE("/img/samples/color_green.gif", "green", 30, 30)

))

Traffic Lights for Status

This formula displays a green, yellow, or red traffic light images to indicate status, using a custom picklist

field called Project Status. Use this formula in list views and reports to create a “Status Summary”

dashboard view.

IMAGE(

CASE(Project_Status__c,

"Green", "/img/samples/light_green.gif",

"Yellow", "/img/samples/light_yellow.gif",

"Red", "/img/samples/light_red.gif",

"/s.gif"),

"status color")

Stars for Ratings

This formula displays a set of one to five stars to indicate a rating or score.

IMAGE(

CASE(Rating__c,

"1", "/img/samples/stars_100.gif",

"2", "/img/samples/stars_200.gif",

"3", "/img/samples/stars_300.gif",

"4", "/img/samples/stars_400.gif",

"5", "/img/samples/stars_500.gif",

"/img/samples/stars_000.gif"),

"rating")

Sample Image Link FormulasExamples of Advanced Formula Fields

Consumer Reports

™

—Style Colored Circles for Ratings

This formula displays a colored circle to indicate a rating on a scale of one to five, where solid red is one,

half red is two, black outline is three, half black is four, and solid black is five.

IMAGE(

CASE(Rating__c,

"1", "/img/samples/rating1.gif",

"2", "/img/samples/rating2.gif",

"3", "/img/samples/rating3.gif",

"4", "/img/samples/rating4.gif",

"5", "/img/samples/rating5.gif",

"/s.gif"),

"rating")

Horizontal Bars to Indicate Scoring

This formula displays a horizontal color bar (green on a white background) of a length that is proportional

to a numeric score. In this example, the maximum length of the bar is 200 pixels.

IMAGE("/img/samples/color_green.gif", "green", 15, Industry_Score__c

* 2) &

IMAGE("/s.gif", "white", 15,

200 - (Industry_Score__c * 2))

Sample Integration Link Formulas

Use these formulas for integration links.

For details about using the functions included in these samples, see Formula Operators and Functions by

Context on page 32.

Application API Link

This formula creates a link to an application outside Salesforce, passing the parameters so that it can

connect to Salesforce via SOAP API and create the necessary event.

HYPERLINK ("https://www.myintegration.com?sId=" & GETSESSIONID() &

"?&rowID=" & Name & "action=CreateTask","Create a Meeting Request")

Important: $Api.Session_ID and GETSESSIONID() return the same value, an identifier

for the current session in the current context. This context varies depending on where the global

variable or function is evaluated. For example, if you use either in a custom formula field, and that

field is displayed on a standard page layout in Salesforce Classic, the referenced session is a basic

Salesforce session. That same field (or the underlying variable or formula result), when used in a

Visualforce page, references a Visualforce session instead.

Session contexts are based on the domain of the request. That is, the session context changes

whenever you cross a hostname boundary, such as from .salesforce.com to

.vf.force.com or .lightning.force.com.

Sample Integration Link FormulasExamples of Advanced Formula Fields

Session identifiers from different contexts, and the sessions themselves, are different. When you

transition between contexts, the new session replaces the previous one, and the previous session

is no longer valid. The session ID also changes at this time.

Normally Salesforce transparently handles session hand-off between contexts, but if you’re passing

the session ID around yourself, you must reaccess $Api.Session_ID or GETSESSIONID()

from the new context to ensure a valid session ID.

Not all sessions are created equal. In particular, sessions obtained in a Lightning Experience context

have reduced privileges, and don't have API access. You can't use these session IDs to make API

calls. {!$Api.Session_ID} isn’t generated for guest users.

Shipment Tracking Integration

This formula creates a link to FedEx, UPS, or DHL shipment tracking websites, depending on the value of

a Shipping Method custom picklist field. The parameters shown in this example for FedEx, UPS,

and DHL websites are illustrative and don’t represent the correct parameters for all situations.

CASE(Shipping_Method__c,

"Fedex",

HYPERLINK("http://www.fedex.com/Tracking?ascend_header=1&clienttype

=dotcom&cntry_code=us&language=english&tracknumbers= "&

tracking_id__c,"Track"),

"UPS",

HYPERLINK("http://wwwapps.ups.com/WebTracking/processInputRequest?HTMLVersion

=5.0&sort_by=status&loc=en_US&InquiryNumber1= "& tracking_id__c &

"&track.x=32&track.y=7", "Track") ,

"DHL",

HYPERLINK("http://track.dhl-usa.com/TrackByNbr.asp?ShipmentNumber=" &

tracking_id__c,"Track"), "")

Skype

™

Auto Dialer Integration

This formula creates a linkable phone number field that automatically dials the phone number via the

Skype VOIP phone application. It requires installation of the Skype application (a third-party product not

provided by Salesforce) on your desktop.

HYPERLINK("callto://+" & Country_Code__c & Phone_Unformatted__c, Phone)

Sample Lead Management Formulas

Use these formulas to manage leads.

For details about using the functions included in these samples, see Formula Operators and Functions on

page 32.

Sample Lead Management FormulasExamples of Advanced Formula Fields

Lead Aging (for open leads)

This formula checks to see if a lead is open and if so, calculates the number of days it has been open by

subtracting the date and time created from the current date and time. The result is the number of days

open rounded to zero decimal places. If the lead isn’t open, this field is blank.

IF(ISPICKVAL(Status,

"Open"), ROUND(NOW()-CreatedDate, 0), null)

Lead Data Completeness

This formula calculates the percent of certain lead fields that your sales personnel enter. The formula field

checks the values of two custom number fields: Phone and Email. If the fields are empty, the formula

returns the value “0.” The formula returns a value of “1” for each field that contains a value and multiplies

this total by fifty to give you the percentage of fields that contain data.

(IF(Phone = "", 0, 1) + IF(Email = "", 0, 1) ) * 50

Lead Numbering

This formula returns a number value for the text value in the auto-number field Lead Number, which

can be useful if you want to use the Lead Number field in a calculation, such as round-robin or other

routing purposes. Auto-number fields are text fields and must be converted to a number for numeric

calculations.

VALUE(Lead_Number__c)

Round-Robin Assignment of Cases or Leads

The following formula example for leads assumes you have three lead queues and you want to assign an

equal number of incoming leads to each queue. You can also assign cases using a similar formula.

MOD(VALUE(Lead_Number__c),

This formula is for a custom formula field named Round_Robin_ID that assigns each lead a value of 0, 1,

or 2. This formula uses a custom auto-number field called Lead Number that assigns each lead a

unique number starting with 1. The MOD function divides the lead number by the number of lead queues

available (three in this example) and returns a remainder of 0, 1, or 2. Use the value of this formula field

in your lead assignment rules to assign lead records to different queues. For example:

•

Round_Robin_ID = 0 is assigned to Queue A

•

Round_Robin_ID = 1 is assigned to Queue B

•

Round_Robin_ID = 2 is assigned to Queue C

Sample Metric Formulas

Use these formulas for metric temperature and metric unit of measure conversion.

For details about using the functions included in these samples, see Formula Operators and Functions by

Context on page 32.

Sample Metric FormulasExamples of Advanced Formula Fields

Temperature Conversion

This formula converts Celsius degrees to Fahrenheit.

1.8 * degrees_celsius__c + 32

Unit of Measure Conversion

This formula converts miles to kilometers.

Miles__c * 1.60934

Sample Opportunity Management Formulas

Use these formulas for business expenses and earnings.

For details about using the functions included in these samples, see Formula Operators and Functions on

page 32.

Expected Product Revenue

This formula calculates total revenue from multiple products, each with a different probability of closing.

ProductA_probability__c * ProductA_revenue__c + ProductB_probability__c

* ProductB_revenue__c

Maintenance Calculation

This formula calculates maintenance fees as 20% of license fees per year. Maintenance Years is a

custom field on opportunities.

Amount * Maint_Years__c * 0.2

Monthly Subscription-Based Calculated Amounts

This formula calculates an opportunity amount based on a monthly subscription rate multiplied by the

subscription period.

Monthly_Amount__c * Subscription_Months__c

Monthly Value

This formula divides total yearly value by 12 months.

Total_value__c / 12

Sample Opportunity Management FormulasExamples of Advanced Formula Fields

Opportunity Additional Costs

This formula calculates the sum of the product Amount, maintenance amount, and services fees. Maint

amount and Service Fees are custom currency fields.

Amount + Maint_Amount__c +

Services_Amount__c

Opportunity Categorization

This formula uses conditional logic to populate an Opportunity category text field, based on

the value of the Amount standard field. Opportunities with amounts less than $1500 are “Category 1,”

opportunities with amounts between $1500 and $10,000 are “Category 2,” and the rest are “Category 3.”

This example uses nested IF statements.

IF(Amount < 1500, "Category 1", IF(Amount > 10000, "Category 3",

"Category 2"))

Opportunity Data Completeness

This formula takes a group of fields and calculates what percent of them are being used by your personnel.

This formula field checks five fields to see if they’re blank. If so, a zero is counted for that field. A “1” is

counted for any field that contains a value, and this total is divided by five (the number of fields evaluated).

This formula requires you to select the Treat blank fields as blanks option under Blank Field Handling

while the Advanced Formula subtab is showing.

(IF(ISBLANK(Maint_Amount__c), 0, 1) +

IF(ISBLANK(Services_Amount__c), 0,1) +

IF(ISBLANK(Discount_Percent__c), 0, 1) +

IF(ISBLANK(Amount), 0, 1) +

IF(ISBLANK(Timeline__c), 0, 1)) / 5

Opportunity Expected License Revenue

This formula calculates expected revenue for licenses based on the probability of closing.

Expected_rev_licenses__c * Probability

Opportunity Revenue Text Display

This formula returns the expected revenue amount of an opportunity in text format without a dollar sign.

For example, if the Expected Revenue of a campaign is “$200,000,” this formula field displays

“200000.”

TEXT(ExpectedRevenue)

Opportunity Total Deal Size

This formula calculates the sum of maintenance and services amounts.

Amount + Maint_Amount__c + Services_Amount__c

Sample Opportunity Management FormulasExamples of Advanced Formula Fields

Opportunity Total Price Based on Units

This formula generates proposal pricing based on unit price and total volume.

Unit_price__c * Volume__c * 20

Professional Services Calculation

This formula estimates professional service fees at an average loaded rate of $1200 per day. Consulting

Days is a custom field on opportunities.

Consulting_Days__c * 1200

Stage-Based Sales Document Selection

This formula Identifies a relevant document in the Documents tab based on opportunity Stage. Use

document IDs in the form of “00l30000000j7AO.”

CASE(StageName,

"Prospecting", "Insert 1st Document ID",

"Qualification", "Insert 2nd Document ID",

"Needs Analysis", "Insert 3rd Document ID",

"Value Proposition", ...

)

Sales Coach

This formula creates a hyperlink that opens a stage-specific document stored in the Documents tab. It

uses the previously defined custom formula field that identifies a document based on opportunity Stage.

See Stage-Based Sales Document Selection on page 30.

HYPERLINK("/servlet/servlet.FileDownload?file=" & Relevant_Document__c,

"View Document in New Window")

Shipping Cost by Weight

This formula calculates postal charges based on weight.

package_weight__c * cost_lb__c

Shipping Cost Percentage

This formula calculates shipping cost as a fraction of total amount.

Ship_cost__c / total_amount__c

Sample Opportunity Management FormulasExamples of Advanced Formula Fields

Tiered Commission Rates

This formula calculates the 2% commission amount of an opportunity that has a probability of 100%. All

other opportunities have a commission value of zero.

IF(Probability = 1,

ROUND(Amount * 0.02, 2),

Total Contract Value from Recurring and Non-Recurring Revenue

This formula calculates both recurring and non-recurring revenue streams over the lifetime of a contract.

Non_Recurring_Revenue__c + Contract_Length_Months__c *

Recurring_Revenue__c

Sample Pricing Formulas

Use these formulas for total amounts and user pricing.

For details about using the functions included in these samples, see Formula Operators and Functions by

Context on page 32.

Total Amount

This formula calculates a total amount based on unit pricing and total units.

Unit_price__c * Total_units__c

User Pricing

This formula calculates a price per user license.

Total_license_rev__c / Number_user_licenses__c

Sample Scoring Calculations Formulas

Use these formulas for lead scoring and customer success scoring.

For details about using the functions included in these samples, see Formula Operators and Functions by

Context on page 32.

Lead Scoring

This formula scores leads, providing a higher score for phone calls than website requests.

CASE(LeadSource, "Phone", 2, "Web", 1, 0)

Here's a formula that scores a lead based on his or her rating:

CASE(1, IF(ISPICKVAL(Rating, "Hot"), 1, 0), 3, IF(ISPICKVAL(Rating,

"Warm"), 1, 0), 2, IF(ISPICKVAL(Rating, "Cold"), 1, 0), 1))

Sample Pricing FormulasExamples of Advanced Formula Fields

Customer Success Scoring

This formula uses a simple scoring algorithm to rank customers a high score for positive survey results in

Salesforce.

Survey_Question_1__c * 5 + Survey_Question_2__c *2

Formula Operators and Functions by Context

Use these operators and functions when building formulas. All functions are available everywhere that

you can include a formula, such as formula fields, validation rules, approval processes, and workflow rules,

unless otherwise specified.

Within an email template, merge fields can only be used in formula functions and operations when the

merge field belongs to the record that the email is related to. Otherwise, these fields don’t resolve.

Extraneous spaces in these samples are ignored.

•

Math Operators

•

Logical Operators

•

Text Operators

•

Date and Time Functions

•

Logical Functions

•

Math Functions

•

Text Functions

•

Summary Functions

•

Advanced Functions

Math Operators

DescriptionOperator

Calculates the sum of two values.+ (Add)

Calculates the difference of two values.- (Subtract)

Multiplies its values.* (Multiply)

Divides its values./ (Divide)

Raises a number to the power of a specified number.^ (Exponentiation)

Specifies that the expressions within the open parenthesis and close parenthesis

are evaluated first. All other expressions are evaluated using standard operator

precedence.

() (Open Parenthesis

and Closed

Parenthesis)

Formula Operators and Functions by ContextExamples of Advanced Formula Fields

Logical Operators

DescriptionOperator

Evaluates if two values are equivalent. The = and == operators are

interchangeable.

= and == (Equal)

Evaluates if two values aren’t equivalent.<> and != (Not Equal)

Evaluates if a value is less than the value that follows this symbol.< (Less Than)

Evaluates if a value is greater than the value that follows this symbol.> (Greater Than)

Evaluates if a value is less than or equal to the value that follows this symbol.<= (Less Than or

Equal)

Evaluates if a value is greater than or equal to the value that follows this symbol.>= (Greater Than or

Equal)

Evaluates if two values or expressions are both true. Use this operator as an

alternative to the logical function AND.

&& (And)

Evaluates if at least one of multiple values or expressions is true. Use this operator

as an alternative to the logical function OR.

|| (Or)

Text Operators

DescriptionOperator

Connects two or more strings.& and +

(Concatenate)

Date and Time Functions

DescriptionFunction

Returns the date that is the indicated number of months before or after a

specified date. If the specified date is the last day of the month, the resulting

ADDMONTHS

date is the last day of the resulting month. Otherwise, the result has the same

date component as the specified date.

Returns a date value from the year, month, and day values that you enter.

Salesforce displays an error on the detail page if the value of the DATE function

in a formula field is an invalid date, such as February 29 in a non-leap year.

DATE

Returns a date value for a date/time or text expression.DATEVALUE

Returns a year, month, day, and GMT time value.DATETIMEVALUE

Returns a day of the month in the form of a number from 1 through 31.DAY

Formula Operators and Functions by ContextExamples of Advanced Formula Fields

DescriptionFunction

Returns the day of the calendar year in the form of a number from 1 through

366.

DAYOFYEAR

Formats the number of seconds with optional days, or the difference between

times or dateTimes as HH:MI:SS.

FORMATDURATION

Returns the local time hour value without the date in the form of a number from

1 through 24.

HOUR

Returns the ISO 8601-week number, from 1 through 53, for the given date,

ensuring that the first week starts on a Monday.

ISOWEEK

Returns the ISO 8601 week-numbering year in 4 digits for the given date, ensuring

that the first day is a Monday.

ISOYEAR

Returns a milliseconds value in the form of a number from 0 through 999.MILLISECOND

Returns a minute value in the form of a number from 0 through 60.MINUTE

Returns the month, a number from 1 (January) through 12 (December) in number

format of a given date.

MONTH

Returns a date/time representing the current moment.NOW

Returns a seconds value in the form of a number from 0 through 60.SECOND

Returns a time value in GMT representing the current moment. Use this function

instead of the NOW function if you only want to track time, without a date.

TIMENOW

Returns the time value without the date, such as business hours.TIMEVALUE

Returns the current date as a date data type.TODAY

Returns the number of seconds since 1 Jan 1970 for the given date, or number

of seconds in the day for a time.

UNIXTIMESTAMP

Returns the day of the week for the given date, using 1 for Sunday, 2 for Monday,

through 7 for Saturday.

WEEKDAY

Returns the four-digit year in number format of a given date.YEAR

Logical Functions

DescriptionFunction

Returns a TRUE response if all values are true, and returns a FALSE response if

one or more values are false.

AND

Determines if an expression has a value, and returns a substitute expression if it

doesn’t. If the expression has a value, returns the value of the expression.

BLANKVALUE

Checks a given expression against a series of values. If the expression is equal to

a value in the series, returns the corresponding result. If it isn’t equal to a value

in the series, returns the else_result.

CASE

Formula Operators and Functions by ContextExamples of Advanced Formula Fields

DescriptionFunction

Determines if expressions are true or false. Returns a given value if true and

another value if false.

Determines if an expression has a value, and returns TRUE if it doesn’t. If it contains

a value, returns FALSE.

ISBLANK

Checks if the record is a clone of another record, and returns TRUE if one item is

a clone. Otherwise, returns FALSE.

ISCLONE

Checks if the formula is running during the creation of a new record, and returns

TRUE if it is. If an existing record is being updated, returns FALSE.

ISNEW

Determines if an expression is null (blank), and returns TRUE if it is. If it contains

a value, returns FALSE.

You must use ISBLANK instead of ISNULL in new formulas. ISBLANK has the same

functionality as ISNULL, but also supports text fields. Salesforce continues to

support ISNULL, so you don’t change any existing formulas.

ISNULL

Determines if a text value is a number, and returns TRUE if it is. Otherwise, returns

FALSE.

ISNUMBER

Returns FALSE for TRUE and TRUE for FALSE.NOT

Determines if an expression is null (blank) and returns a substitute expression if

it is. If the expression isn’t blank, returns the value of the expression.

You must use BLANKVALUE instead of NULLVALUE in new formulas. BLANKVALUE

has the same functionality as NULLVALUE, but it also supports text fields.

NULLVALUE

Salesforce continues to support NULLVALUE, so changing the existing formulas

isn’t necessary.

Determines if expressions are true or false. Returns TRUE if any expression is true,

and returns FALSE if all expressions are false.

Returns the previous value of a field.PRIORVALUE

Math Functions

DescriptionFunction

Calculates the absolute value of a number. The absolute value of a number is

the number without its positive or negative sign.

ABS

Returns the arc cosign of the number in radians, if the given number is from -1

through 1. Otherwise, returns NULL.

ACOS

Returns the arc sine of the number in radians, if the given number is from -1

through 1. Otherwise, returns NULL.

ASIN

Returns the arc tangent of the number in radians.ATAN

Returns the arc tangent of the quotient of y and x in radians.ATAN2

Formula Operators and Functions by ContextExamples of Advanced Formula Fields

DescriptionFunction

Rounds a number up to the nearest integer, away from zero if negative.CEILING

Returns a string with the first character’s code point as the given number.CHR

Returns the cosine of the number in radians, if the given number is from -1

through 1. Otherwise, returns NULL.

COS

Calculates the distance between two locations in miles or kilometers.DISTANCE

Returns a value for e raised to the power of a number that you specify.EXP

Returns a number rounded down to the nearest integer, towards zero if negative.FLOOR

Returns the datetime that represents the given number as the seconds elapsed

since 1 Jan 1970.

FROMUNIXTIME

Returns a geolocation based on the provided latitude and longitude. Must be

used with the DISTANCE function.

GEOLOCATION

Returns the natural logarithm of a specified number. Natural logarithms are

based on the constant e value of 2.71828182845904.

Returns the base 10 logarithm of a number.LOG

Returns the highest number from a list of numbers.MAX

Rounds a number up to the nearest integer, towards zero if negative.MCEILING

Rounds a number down to the nearest integer, away from zero if negative.MFLOOR

Returns the lowest number from a list of numbers.MIN

Returns a remainder after a number is divided by a specified divisor.MOD

Returns pi.PI

Returns the number of selected values in a multi-select picklist.PICKLISTCOUNT

Returns the nearest number to a number that you specify, constraining the new

number by a specified number of digits.

ROUND

Returns the sine of the number, where the number is given in radians.SIN

Returns the positive square root of a given number.SQRT

Returns the tangent of the number, where the number is given in radians.TAN

Truncates a number to a specified number of digits.TRUNC

Text Functions

DescriptionFunction

Returns the first character’s code point from the given string as a number.ASCII

Formula Operators and Functions by ContextExamples of Advanced Formula Fields

DescriptionFunction

Determines if text begins with specific characters. Returns TRUE if it does, and

returns FALSE if it doesn't.

BEGINS

Inserts a line break in a string of text.BR

Converts a 15-character ID to a case-insensitive 18-character ID.CASESAFEID

Compares two arguments of text, and returns TRUE if the first argument contains

the second argument. If not, returns FALSE.

CONTAINS

Returns the position of a string within a string of text represented as a number.FIND

Returns the user’s session ID.GETSESSIONID

Encodes text and merge field values for use in HTML by replacing characters

that are reserved in HTML, such as the greater-than sign (>), with HTML entity

equivalents, such as >.

HTMLENCODE

Creates a link to a URL specified that is linkable from the text specified.HYPERLINK

Inserts an image with alternate text and height and width specifications.IMAGE

Determines if any value selected in a multi-select picklist field equals a text literal

that you specify.

INCLUDES

Returns the text as lowercase with the first character of each word in uppercase.INITCAP

Determines if the value of a picklist field is equal to a text literal that you specify.ISPICKVAL

Encodes text and merge field values for use in JavaScript by inserting escape

characters, such as a backslash (\), before unsafe JavaScript characters, such as

the apostrophe (').

JSENCODE

Encodes text and merge field values for use in JavaScript inside HTML tags by

replacing characters that are reserved in HTML with HTML entity equivalents

JSINHTMLENCOD

and inserting escape characters before unsafe JavaScript characters.

JSINHTMLENCODE(someValue) is a convenience function that is

equivalent to JSENCODE(HTMLENCODE((someValue)). That is,

JSINHTMLENCODE first encodes someValue with HTMLENCODE, and

then encodes the result with JSENCODE.

Returns the specified number of characters from the beginning of a text string.LEFT

Returns the number of characters in a specified text string.LEN

Converts all letters in the specified text string to lowercase. Any characters that

aren’t letters are unaffected by this function. Locale rules are applied if a locale

is provided.

LOWER

Inserts characters that you specify to the left-side of a text string.LPAD

Returns the specified number of characters from the middle of a text string given

the starting position.

MID

Returns the characters of a source text string in reverse order.REVERSE

Formula Operators and Functions by ContextExamples of Advanced Formula Fields

DescriptionFunction

Returns the specified number of characters from the end of a text string.RIGHT

Inserts characters that you specify to the right-side of a text string.RPAD

Substitutes new text for old text in a text string.SUBSTITUTE

Converts a percent, number, date, date/time, or currency type field into text

anywhere formulas are used. Also, converts picklist values to text in approval

TEXT

rules, approval step rules, workflow rules, escalation rules, assignment rules,

auto-response rules, validation rules, formula fields, field updates, and custom

buttons and links.

Removes the spaces and tabs from the beginning and end of a text string.TRIM

Converts all letters in the specified text string to uppercase. Any characters that

aren’t letters are unaffected by this function. Locale rules are applied if a locale

is provided.

UPPER

Encodes text and merge field values for use in URLs by replacing characters that

are illegal in URLs, such as blank spaces, with the code that represent those

URLENCODE

characters as defined in RFC 3986, Uniform Resource Identifier (URI): Generic Syntax.

For example, blank spaces are replaced with %20, and exclamation points are

replaced with %21.

Converts a text string to a number.VALUE

Summary Functions

These functions are available with summary, matrix, and joined reports.

DescriptionFunction

Returns the value of a specified parent grouping. A “parent” grouping is any level

above the one containing the formula. You can use this function only in custom

summary formulas and at grouping levels for reports, but not at summary levels.

PARENTGROUPVAL

Returns the value of a specified previous grouping. A “previous” grouping is one

that comes before the current grouping in the report. Choose the grouping level

PREVGROUPVAL

and increment. The increment is the number of columns or rows before the

current summary. The default is 1, the maximum is 12. You can use this function

only in custom summary formulas and at grouping levels for reports, but not at

summary levels.

Advanced Functions

DescriptionFunction

Returns the conversion rate to the corporate currency for the given currency ISO

code. If the currency is invalid, returns 1.0.

CURRENCYRATE

Formula Operators and Functions by ContextExamples of Advanced Formula Fields

DescriptionFunction

Returns an array of strings in the form of record IDs for the selected records in a

list, such as a list view or related list.

GETRECORDIDS

Securely retrieves external images, and prevents unauthorized requests for user

credentials.

IMAGEPROXYURL

Returns content from an s-control snippet. Use this function to reuse common

code in many s-controls.

INCLUDE

Compares the value of a field to the previous value, and returns TRUE if the values

are different. If the values are the same, returns FALSE.

ISCHANGED

Returns a JunctionIDList based on the provided IDs.JUNCTIONIDLIST

Returns a relative URL in the form of a link (href and anchor tags) for a custom

s-control or Salesforce page.

LINKTO

Returns an Einstein Discovery prediction for a record based on the specified

record ID or for a list of fields and their values.

PREDICT

Compares a text field to a regular expression, and returns TRUE if there’s a match.

Otherwise, returns FALSE. A regular expression is a string used to describe a

format of a string according to certain syntax rules.

REGEX

Returns a script tag with source for a URL that you specify. Use this function

when referencing the Lightning Platform AJAX Toolkit or other JavaScript toolkits.

REQUIRESCRIPT

Returns a relative URL for an action, s-control, Visualforce page, or a file in a static

resource archive in a Visualforce page.

URLFOR

Returns a value by looking up a related value on a custom object similar to the

VLOOKUP() Excel function.

VLOOKUP

Formula Operators and Functions by ContextExamples of Advanced Formula Fields