Advanced Formula Fields in Salesforce.
Use the following formula samples when creating custom formula fields. For samples of other types of formulas, see Examples of Validation Rules and Useful Default Field Value Formulas.
This document contains the following categories of custom formula samples:
See Also:About FormulasOperators and FunctionsFormulas: How Do I...Tips on Building FormulasQuick Reference Guide: Formula Fields
Account Management
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"))))))
Contract Aging
This
formula calculates the number of days since a contract with an account
was activated. If the contract Status is not “Activated,” this field is
blank.
IF(ISPICKVAL(Contract_Status__c, "Activated"),
NOW() - Contract_Activated_Date__c, null)
Contract Approval Process Aging
This
formula calculates how many days a contract is in the approval process.
This example is a number formula field on contracts that uses a custom
date field called Date in approval.
TODAY()-Date_in_approval__c
For details about using this function, see TODAY.
Month of Last Account Activity
This formula field displays the month of the last account activity or “None” if there are no activities for the account.
CASE(MONTH(LastActivityDate),
1, "January",
2, "February",
3, "March",
4, "April",
5, "May",
6, "June",
7, "July",
8, "August",
9, "September",
10, "October",
11, "November",
12, "December",
"None")
Month of Service-Level Agreement Expiration
This
formula returns the month that your service-level agreement expires.
This example uses a custom date field called SLA Expiration Date.
MONTH(SLAExpirationDate__c)
For details about using this function, see MONTH.
Account Media Service Links
HYPERLINK(
"http://newssearch.bbc.co.uk/cgi-bin/search/results.pl?scope=newsifs;tab=news;q="&Name,
"BBC News")
HYPERLINK(
"http://www.bloomberg.com/apps/quote?ticker="&TickerSymbol,
"Bloomberg News")
CNN™ News Search
This formula creates a link to a CNN news search site using the Account Name.
HYPERLINK(
"http://websearch.cnn.com/search/search?source=cnn&
invocationType=search%2Ftop&sites=web&query="&Name,
"CNN News")
MarketWatch™ Search
This formula creates a link to an account's ticker symbol on the Marketwatch.com website.
HYPERLINK(
"http://www.marketwatch.com/tools/quotes/quotes.asp?symb="&TickerSymbol,
"Marketwatch")
Google™ Search
This formula creates a link to a Google search site using the Account Name.
HYPERLINK(
"http://www.google.com/search?en&q="&Name,
"Google")
Google News Search
This formula creates a link to a Google news search site using the Account Name
HYPERLINK(
"http://www.google.com/news?en&q="&Name,
"Google News")
Yahoo!™ Search
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")
For details about using the function used in these formulas, see HYPERLINK.
Case Management
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)
For details about using this function, see HYPERLINK.
Case Aging (Assignments)
Use
this example of a custom formula field called Days Open to display
different text depending on the number of days a case has been open:
CASE(Days_Open__c, 3,
"Reassign", 2, "Assign Task", "Maintain")
The following text is displayed:
§ “Reassign” for any case open three days.
§ “Assign Task” for any case open two days.
§ “Maintain” for all other cases.
For details about using this function, see CASE.
Case Aging (Open Cases)
This
formula calculates the number of days a case has been open. If the case
is closed, it sets the result to null. Add this formula to a related
list as the sort column to quickly see which open cases have been open
the longest. The formula returns zero if the case has been open for less
than 24 hours.
IF(IsClosed,
null,
NOW() - CreatedDate )
Case Aging (Open and Closed Cases)
This
formula calculates the number of days a closed case was open or the
number of days an open case has been open since the date the case was
created. The formula returns zero if the case has been open for less
than 24 hours.
IF(IsClosed,
ROUND(ClosedDate - CreatedDate, 0), ROUND((NOW() - CreatedDate),0))
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") )
For details about using this function, see IF.
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
Case Due Date Calculation
This
formula sets the due date of a case based on the priority. If it is
high, the due date is two days after it opens. If it is medium, the due
date is five days after opening. Otherwise, the due date is seven days.
CASE (Priority,
"High", CreatedDate + 2,
"Medium",CreatedDate + 5,
CreatedDate + 7)
For details about using this function, see CASE.
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")
For details about using this function, see CASE.
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",
"")
For details about using this function, see CASE.
Commission Calculations
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)
This
example calculates the commission amount for any opportunity that has a
“Closed Won” stage. The value of this field will be the amount times
0.02 for any closed/won opportunity. Open or lost opportunities will
have a zero commission value.
Commission Deal Size
This
formula calculates a commission rate based on deal size, returning a 9%
commission rate for deals over 100,000 and an 8% commission rate for
smaller deals.
IF(Amount > 100000, 0.09, 0.08 )
For details about using this function, see IF.
This
formula assigns the “YES” value to opportunities with a commission
greater than or equal to one million. Note, this is a text formula field
on opportunities that uses a custom currency field called Commission
IF(Commission__c >= 1000000, "YES", "NO")
For details about using this function, see IF.
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)
For details about using this function, see MAX.
This date formula displays the account's Created Date field on the contacts page.
Account.CreatedDate
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
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")
For details about using this function, see CASE.
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( "http://" & Account.Website , "Click Here" )
)
)
This formula also adds the necessary "http://" or "https://" before a URL if neither were included in the URL field.
For details about using this function, see HYPERLINK.
Contact’s Age
Use
this formula to calculate a person’s age based on a standard field
called Birthdate. The person’s Birthdate is subtracted from today’s
date, which returns the number of days since the person’s Birthdate.
This number is divided by the number of days in a year and rounded down
to the nearest integer.
FLOOR((TODAY()-Birthdate)/365.2425)
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. Click SetupCustomizeContactsButtons and Links.
2. Click New under Custom Buttons and Links
3. Enter a Label for this link, like LinkedInLink.
4. Enter this formula in the content box:
http://www.linkedin.com/search/fpsearch?type=people&keywords={!Contact.FirstName}+{!Contact.LastName}
5. Click Save.
Contact Identification Numbering
This
formula displays the first five characters of the contact’s last name
and the last four characters of the contact’s social security number
separated by a dash. Note that this example uses a text custom field
called SSN on contacts.
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")
For details about using this function, see CASE.
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 withVP 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 calledYahoo Name on contacts where you can store the
contact's Yahoo! Messenger ID.
HYPERLINK("ymsgr:sendIM?"
& Yahoo_Name__c, IMAGE("http://opi.yahoo.com/online?u=" &
Yahoo_Name__c & "&m;=g&t;=0", "Yahoo"))
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 for the account.
CASE(ShippingCountry,
"USA",
ShippingStreet & BR() &
ShippingCity & ",
" & ShippingState & " " &
ShippingPostalCode & BR()
& ShippingCountry,
"France",
ShippingStreet & BR() &
ShippingPostalCode & " " &
ShippingCity & BR() &
ShippingCountry, "etc")
Telephone Country Code
This formula determines the telephone country code of a contact based on the Mailing Country of the mailing address.
CASE(MailingCountry,
"USA", "1",
"Canada", "1",
"France", "33",
"UK", "44",
"Australia", "61",
"Japan", "81",
"?")
For details about using this function, see CASE.
This
formula removes the parentheses and dash characters from North American
phone numbers. This 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)
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")
For details about using this function, see IF.
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)
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.” Note that 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")
This
formula returns the date of a person's birthday in the current year,
even if the person's birthday is on February 29th in a leap year.
IF(AND(MONTH(Birthdate) = 2, DAY(Birthdate) = 29),
(IF(OR(MOD(YEAR(DATEVALUE(NOW())), 400) = 0, AND(MOD(YEAR(DATEVALUE(NOW())) ,4) = 0, MOD(YEAR(DATEVALUE(NOW())), 100) <> 0)),
DATE(YEAR(DATEVALUE(NOW())), MONTH(Birthdate), DAY(Birthdate)),
DATE(YEAR(DATEVALUE(NOW())), MONTH(Birthdate + 1), 28))),
(DATE(YEAR(DATEVALUE(NOW())), MONTH(Birthdate) , DAY(Birthdate))))
Day of Week (number)
This formula calculates today’s day of the week as a number (0 = Sunday, 1 = Monday, 2 = Tuesday, and so on).
MOD(TODAY() - DATE(1900, 1, 7), 7)
Similarly,
this formula substitutes the TODAY() function shown in the previous
example with a custom date field called Sign Up Date. It returns the day
of the week as a number for that field.
MOD(Sign_Up_Date__c - DATE(1900, 1, 7), 7)
Day of Week (text)
This
formula calculates today’s day of the week and displays it as text. To
determine the day of the week for a date field, use the formula below
and replace “TODAY()” with that date field.
CASE(
MOD(TODAY() - DATE(1900, 1, 7), 7),
0, "Sunday",
1, "Monday",
2, "Tuesday",
3, "Wednesday",
4, "Thursday",
5, "Friday",
6, "Saturday", "Error")
TODAY() – DATE(YEAR(TODAY()), 1, 1) + 1
Days Until End of Month
This formula displays the number of days between a specific date and the end of the month in which the date occurs.
IF(
MONTH(CloseDate)=12,
DATE(YEAR(CloseDate),12,31) - CloseDate,
DATE(YEAR(CloseDate),
MONTH(CloseDate)+1,1) - CloseDate-1)
Time of Day
MID (TEXT (Due_Date_Time__c), 12, 5)
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 do not equal the
opportunity Amount standard field value. Otherwise, it displays "Full
Price."
IF(Maintenance_Amount__c + Services_Amount__c <> Amount,
"Discounted",
"Full Price")
For details about using this function, see IF.
Opportunity Discount Amount
This
formula calculates the difference of the opportunity Amount less
the Discount Amount. Note that Discount Amount is a custom currency
field on opportunities.
Amount
- Discount_Amount__c
For details about using this operator, see - (Subtract).
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)
For details about using this function, see ROUND.
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)
This
example determines an employee's bonus amount based on the smallest of
two amounts: the employee's gross times bonus percent or an equally
divided amount of the company's performance amount among all employees.
It assumes you have 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)
For details about using this function, see MIN.
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 custom
currency field forContribution.
MIN(250, Contribution__c /2)
For details about using this function, see MIN.
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
For details about using this operator, see + (Add).
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)
For details about using this function, see IF.
This
formula displays the text “Expense-” followed by trip name and the
expense number. This is a text formula field that uses an expense number
custom field.
"Expense-"
& Trip_Name__c & "-" & ExpenseNum__c
For details about using this operator, see - (Subtract).
Miles_Driven__c * 0.35
For details about using this operator, see * (Multiply).
Principal__c * ( 1 + Rate__c / M ) ^ ( T * M) )
For details about using these operators, see * (Multiply), / (Divide), and ^ (Exponentiation).
Compound Interest Continuous
This formula calculates the interest that will have accumulated after T years, if continuously compounded.
Principal__c * EXP(Rate__c * T)
For details about using this function, see EXP.
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. Note that Consulting Days is a custom field on
opportunities.
Consulting_Days__c
* 1200
For details about using this operator, see * (Multiply).
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
For details about using this operator, see - (Subtract).
Margin_percent__c * Items_Sold__c * Price_item__c
For details about using this operator, see * (Multiply).
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 on contracts.
(BLANKVALUE(Payment_Due_Date__c, StartDate
+5)
For details about using this function, see BLANKVALUE.
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 Dateand a text custom field called Payment Status on
contracts.
IF(
AND(Payment_Due_Date__c < TODAY(),
ISPICKVAL(Payment_Status__c, "UNPAID")),
"PAYMENT OVERDUE",
null )
Yahoo! Instant Messenger™ Image
This
formula displays an image that indicates whether a contact or user is
currently 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", " ")))
“Skype Me™” Auto Dialer Button
This
formula displays an image that looks like a push button. Clicking the
button automatically dials the specified phone number.
HYPERLINK("callto://" & "+1" & Phone,
IMAGE("http://goodies.skype.com/graphics/skypeme_btn_small_blue.gif",
"Click to Skype"))
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")
For details about using this function, see IMAGE.
This formula displays a 30 x 30 pixel image of a red, yellow, or green, depending on the value of a Case Age custom text 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")
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))
For details about using this function, see IMAGE.
This
formula creates a link to an application outside Salesforce, passing
the parameters so that it can connect to Salesforce via the Web
services API and create the necessary event.
HYPERLINK
("https://www.myintegration.com?sId=" & GETSESSIONID() &
"?&rowID=" & Name & "action=CreateTask","Create a Meeting
Request")
For details about using these functions, see HYPERLINK and GETSESSIONID.
This
formula creates a link to FedEx, UPS, or DHL shipment tracking
websites, depending on the value of a Shipping Method custom picklist
field. Note that the parameters shown in this example for FedEx, UPS,
and DHL websites are illustrative and do not 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"), "")
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.com) on your desktop.
HYPERLINK("callto://+" & Country_Code__c & Phone_Unformatted__c, Phone)
For details about using this function, see HYPERLINK.
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 is not open, this field is
blank.
IF(ISPICKVAL(Status,
"Open"), ROUND(NOW()-CreatedDate, 0), null)
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
For details about using this function, see IF.
This
formula returns a number value for the text value in the auto-number
field Lead Number. This can be useful if you want to use the Lead
Number field in a calculation, such as round-robin or other routing
purposes. Note that auto-number fields are text fields and must be
converted to a number for numeric calculations.
VALUE(Lead_Number__c)
For details about using this function, see VALUE.
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),
3)
Metrics
1.8 * degrees_celsius__c + 32
For details about using these operators, see * (Multiply) and + (Add).
Miles__c/.621371192
For details about using this operator, see / (Divide).
Expected_close_date__c -TODAY()
For details about using this function, see TODAY.
This
formula returns the month in text for the close date of an opportunity.
Use this example when building a custom report that groups
opportunities by the month of the Close Date.
CASE(
MONTH(CloseDate),
1, "January",
2, "February",
3, "March",
4, "April",
5, "May",
6, "June",
7, "July",
8, "August",
9, "September",
10, "October",
11, "November",
12, "December",
"Invalid month")
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
For details about using these operators, see * (Multiply) and + (Add).
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
For details about using this operator, see * (Multiply).
This formula calculates an opportunity amount based on a monthly subscription rate multiplied by the subscription period.
Monthly_Amount__c * Subscription_Months__c
For details about using this operator, see * (Multiply).
Total_value__c / 12
For details about using this operator, see / (Divide).
Opportunity Additional Costs
This
formula calculates the sum of the opportunity Amount, maintenance
amount, and services fees. Note that Maint amount and Service Fees are
custom currency fields on opportunities.
Amount
+ Maint_Amount__c + Services_Amount__c
For details about using this operator, see + (Add).
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,” those between $1500 and
$10000 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"))
For details about using this function, see IF.
This
formula takes a group of opportunity fields and calculates what percent
of them are being used by your sales personnel. This formula field
checks five fields to see if they are 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). Note
that this formula requires you 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
For details about using this function, see ISBLANK.
Expected_rev_licenses__c * Probability
For details about using this operator, see * (Multiply).
This
formula creates reminder date based on seven days before the close date
of an opportunity. Use this formula field in a workflow rule to create
an event for the appropriate user to take action.
Reminder Date = CloseDate - 7
For details about using these operators, see = and == (Equal) and - (Subtract).
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)
For details about using this function, see TEXT.
This
formula splits opportunity amount between multiple sales
representatives. The total reps custom field indicates the total number
of representatives on the deal.
Amount / total_reps__c
For details about using this operator, see / (Divide).
Amount + Maint_Amount__c + Services_Amount__c
For details about using this operator, see + (Add).
Unit_price__c * Volume__c * 20
For details about using this operator, see * (Multiply).
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
For details about using this operator, see * (Multiply).
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", …
)
)
For details about using this function, see CASE.
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.
HYPERLINK("/servlet/servlet.FileDownload?file=" & Relevant_Document__c, "View Document in New Window")
For details about using this function, see HYPERLINK.
package_weight__c * cost_lb__c
For details about using this operator, see * (Multiply).
Ship_cost__c / total_amount__c
For details about using this operator, see / (Divide).
This
formula calculates the 2% commission amount of an opportunity that has a
probability of 100%. All other opportunities will have a commission
value of zero.
IF(Probability = 1,
ROUND(Amount * 0.02, 2),
0)
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
For details about using these operators, see + (Add) and * (Multiply).
Unit_price__c * Total_units__c
For details about using this operator, see * (Multiply).
Total_license_rev__c / Number_user_licenses__c
For details about using this operator, see / (Divide).
This
formula calculates intermediate milestone dates by subtracting days
from the end date (for projects that are planned based on end date).
Release_Date__c - 7 * Phase_duration_in_weeks__c
For details about using this operator, see * (Multiply).
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))
For details about using this function, see CASE.
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
For details about using these operators, see* (Multiply) and + (Add).
9 comments:
We’ve been stumbling around the internet and found your blog along the way.
We love your work! What a great corner of the internet :)
http://24x7direct.com.au/
This Advanced Formula Fields in Salesforce are just awesome. Thanks for sharing it with us.Salesforce Implementation.
thank you....
Server and storage
Server and Storage Solutions
thanks so much your blog is really helpful Buy USA Phone Numbers Online
The information which you have provided is very good. It is very useful who is looking for
Big data consulting services Singapore
Data Warehousing services Singapore
Data Warehousing services
Data migration services Singapore
Data migration services
Thank you for sharing this best salesforce billing training online
salesforce billing admin training online
salesforce billing training in ameerpet
learn salesforce billing online
salesforce billing course online
You need personal informations from companies,family and friends that will better your life and you need easy access without them noticing just contact james or you’re financially unstable or you have a bad credit score, he will solve that without stress,he and his team can clear criminal records without leaving a trace and can also anonymously credit your empty credit cards with funds you need,all these are not done free obviously but I like working with James and his team cause they keep you updated on every step taken in order to achieve the goal and they also deliver on time,I tested and confirmed this I’m still happy on how my life is improving after my encounter with him ,you can send a mail to premiumhackservices@gmail.com
Post a Comment