Formula fields in Salesforce with examples

Formula fields in Salesforce are your secret weapon for automating calculations, manipulating data, and enhancing user experience directly within your Salesforce org. They allow you to derive values from existing fields, apply logic, and display dynamic information, all without writing a single line of code. This blog post will dive deep into the world of Formula fields in Salesforce, exploring various operators and functions with practical examples.

What are Formula Fields in Salesforce?

Formula fields in Salesforce are read-only fields that derive their values from a formula you define. Think of it as a “smart field” that dynamically updates itself using rules you define. This formula can reference:

• Other Fields: Reference standard or custom fields within the same object or related objects.
• Operators: Use mathematical operators ( example : +, -, *, /), logical operators (example :  &&, ||, !), and comparison operators (example : =, <, >, !=).
• Functions: Leverage built-in Salesforce functions for text manipulation, date/time calculations, logical evaluations, and more.

Benefits of Formula Fields in Salesforce:

• To Automate calculations like tax rates, discounts, or lead scores.
• Convert data into a more user-friendly format (example: combining first and last names).
• Display different values based on specific criteria.
• Create custom fields that are readily available for reporting.

Data Types Returned by Formula Fields in Salesforce:

Formula fields can output different types of data depending on your needs. Here are the 8 formula return types supported in Salesforce:

• Checkbox: Returns true(checked) or false(unchecked), displayed as a checkbox in records and reports.
• Currency: Returns a number with up to 18 digits and a currency symbol (e.g. $10.00). Rounds values using the half-up rule. For example, 23.5 would be rounded up to 24.
• Date: Returns a calendar date. Use TODAY() to get the current date.
• Date/Time: Returns a date and time value, including hour, minute, and second. Use NOW() for the current timestamp.
• Number: Returns an integer or decimal of up to 18 digits, using round-half-up rules. For example, 12.345 would be rounded up to 12.35.
• Percent: Returns a percentage value, stored as a decimal. For example, 90% is represented as 0.90.
• Text: Returns a string of up to 3,900 characters. Use quotes to include static text. For example, if Status__c is “Approved”, the formula “Status: ” & Status__c would return “Status: Approved”.
• Time: Returns only the time (hour, minute, second, millisecond).

Creating Your First Formula Field

Step-by-Step Process:

• Step 1:
  Log in to your Salesforce org. Click the gear icon in the top right corner, then select Setup to open the Setup page.
• Step 2:
  In the Setup menu, click on Object Manager to view all your objects.
• Step 3:
  From the list, select the object where you want to create the new formula field.
• Step 4:
  On the object’s page, go to the Fields & Relationships tab and click the New button to start creating a new field.
• Step 5:
  Choose Formula as the field type and click Next.
• Step 6:
  Enter the “Field Label” and the “Field Name” is auto-generated based on the label.
• Step 7:
  Select the formula return type.
• Step 8:
  Set decimal places(if applicable for number, currency, or percent fields)
• Step 9:
  Build your formula:
  • Use the formula editor to add fields, operators, functions, and your logic.
  • Use the Check Syntax button to make sure your formula is error-free.
  • Optionally, add a Description to explain the purpose of the field
  • Use Help Text if you want to guide users who interact with this field
• Step 10:
  Configure field-level security(FLS) and add the new formula field to page layouts.

Example:

Here’s a practical example of a formula field being created in the editor.

In this use case, we’re setting up a checkbox field called Partner__c that automatically checks itself if the Type field on the Account is set to “Partner“. The formula uses the IF function combined with ISPICKVAL to check the value of the Type field.

Operators: The Building Blocks of Salesforce Formula Fields

Operators are symbols that perform specific operations on values or fields. Let’s explore examples of frequently used operators:

I. Mathematical Operators

• + (Add):
  • Use Case: Calculate the total price, including tax.
  • Example: Price__c + GST__c
• – (Subtract):
  • Use Case: Calculate the discount amount.
  • Example: ListPrice__c – SellingPrice__c
• * (Multiply):
  • Use Case: Calculate the total cost of items.
  • Example: Quantity__c * UnitPrice__c
• / (Divide):
  • Use Case: Calculate the percentage of completion.
  • Example: TasksCompleted__c / TotalTasks__c
• () (Parentheses):
  • Use Case: Control the order of operations in a complex calculation.
  • Example: (Price__c – Discount__c) * Quantity__c

II. Logical Operators

• && (AND):
  • Use Case: Check if both conditions are true.
  • Example: IsClosed && (CloseDate < TODAY())
• || (OR):
  • Use Case: Check if at least one condition is true.
  • Example: StageName = “Closed Won” || StageName = “Closed Lost”
• ! or NOT():
  • Use Case: Reverse the value of a logical expression.
  • Example (using !): !IsProcessed__c
  • Example (using NOT()): NOT(ISBLANK(Email))

III. Comparison Operators

• = or == (Equal to):
  • Use Case: Check if a field value is equal to a specific value.
  • Example: RecordType.Name = “Customer Account”
  • Example: TEXT(Account.Type) == “Partner”
• <> or != (Not equal to):
  • Use Case: Check if a field value is not equal to a specific value.
  • Example (<>): TEXT(Status__c ) <> “Completed”
  • Example (!=): Profile.Name != ‘Sales’
• < (Less than):
  • Use Case: Check if a numeric or date/time value is less than another.
  • Example: CloseDate < TODAY()
• > (Greater than):
  • Use Case: Check if a numeric or date/time value is greater than another.
  • Example: Opportunity.Amount > 50000
• <= (Less than or equal to):
  • Use Case: Check if a numeric or date/time value is less than or equal to another.
  • Example: ApprovalScore__c <= 75
• >= (Greater than or equal to):
  • Use Case: Check if a numeric or date/time value is greater than or equal to another.
  • Example: SLAExpirationDate__c >= TODAY() + 7

IV. Text Operators

• & (Concatenate):
  • Use Case: Combine text values from different fields or strings.
  • Example: FirstName & ” ” & LastName

Functions: The Power Tools of Salesforce Formula Fields

Functions are built-in tools used to perform specific actions. Let’s explore examples of frequently used functions:

I. Logical Functions

• AND(logical1, logical2, …) – Returns TRUE if all arguments are true.
  • Use Case: Checks if both price and quantity exceed certain values.
  • Example: AND(Price__c > 10, Quantity__c > 1)
• OR(logical1, logical2, …) – Returns TRUE if any argument is true.
  • Use Case: Checks if the opportunity is in a winning or near-winning stage.
  • Example: OR(StageName = “Closed Won”, StageName = “Sales Won”)
• NOT(logical) – Reverses the value of logical.
  • Use Case: Checks if the opportunity is not closed.
  • Example: NOT(IsClosed)
• IF(logical, value_if_true, value_if_false) – Returns one value if true, another if false.
  • Use Case: Categorises opportunities based on their amount.
  • Example: IF(Amount > 100000, “High Value”, “Standard Value”)
• ISBLANK(expression) – Returns TRUE if the expression is blank.
  • Use Case: Checks if the description field is empty.
  • Example: ISBLANK(Description)
• ISNULL(expression) – Returns TRUE if the expression is null.
  • Use Case: Checks if the opportunity Amount is null.
  • Example: ISNULL(Amount)
• ISNUMBER(text) – Returns TRUE if the text is a number.
  • Use Case: Checks if the discount percentage field contains a valid number.
  • Example: ISNUMBER(Discount_Percentage__c)
• CASE(expression, value1, result1, value2, result2, …, else_result) – Returns a value based on a series of comparisons.
  • Use Case: Categorises leads based on their source.
  • Example:CASE(LeadSource,
    “Web”, “Online Lead”,
    “Phone”, “Direct Inquiry”,
    “Referral”, “Word of Mouth”,
    “Other”)

• NULLVALUE(expression, substitute_value) – Returns the substitute_value if the expression is null; otherwise, it returns the value of the expression.
  • Use Case: Use the billing city as the shipping city if the shipping city is missing.
  • Example: NULLVALUE(Account.ShippingCity, Account.BillingCity)
• BLANKVALUE(expression, substitute_value) – Returns the substitute_value if the expression is blank; otherwise, it returns the value of the expression. A field is blank if it contains no value.
  • Use Case: Show “No description provided” when the account description is empty.
  • Example: BLANKVALUE(Case.Description, “No description provided.”)

II. Mathematical Functions

• ABS(number) – Returns the absolute value(positive) of a number.
  • Use Case: Calculate the absolute difference between expected and target revenue.
  • Example: ABS(ExpectedRevenue__c – TargetRevenue__c) = ABS(-9000) = 9000
• CEILING(number) – Rounds a number up to the nearest integer.
  • Use Case: Calculate the minimum number of boxes needed.
  • Example: CEILING(Number_of_Items__c / Items_Per_Box__c) = CEILING(4.2) = 5
• FLOOR(number) – Rounds a number down to the nearest integer.
  • Use Case: Calculate the number of full days from the total hours.
  • Example: FLOOR(Total_Hours__c / 24) = FLOOR(24.6) = 24
• MAX(number1, number2, …) – Returns the largest of the numbers.
  • Use Case: Compares the opportunity amount with the account’s annual revenue.
  • Example: MAX(Opportunity.Amount, Account.AnnualRevenue) = MAX(10,30) = 30
• MIN(number1, number2, …) – Returns the smallest of the numbers.
  • Use Case: Compares the cost and price of a product.
  • Example: MIN(Cost__c, Price__c) = MIN(20,10) = 10
• MOD(number, divisor) – Returns the remainder of a division.
  • Use Case: Checks if a record number is even or odd.
  • Example: MOD(Record_Number__c, 2) = MOD(9,2) = 1
• ROUND(number, decimal_places) – Rounds a number to the specified number of decimal places.
  • Use Case: Rounds the average rating to one decimal place
  • Example: ROUND(Average_Rating__c, 1) = ROUND(10.74, 1) = 10.7
• TRUNC(number,num_digits) – Truncates a number to a specified number of decimal places.
  • Use Case: Truncates the average rating to two decimal places without rounding
  • Example: TRUNC(Average_Rating__c, 2) = TRUNC(10.7467, 2) = 10.74

III. Core Text Functions

• LEFT(text, num_chars): Returns the specified number of characters from the left of a text string.
  • Use Case: Extracts the first three characters of a product code
  • Example: LEFT(Product_Code__c, 3) = LEFT(“AB99C”,3) = “AB9”
• RIGHT(text, num_chars): Returns the specified number of characters from the right of a text string.
  • Use Case: Extracts the last four digits of a phone number
  • Example: RIGHT(Phone, 4) = RIGHT(“9876501234”,4) = “1234”
• MID(text, start_num, num_chars): Returns a substring from the middle of a text string.
  • Use Case: Extracts three characters starting from the fourth character of a serial number.
  • Example: MID(Serial_Number__c, 4, 3) = MID(“SN-AB123”,4,3) = “AB1”
• LEN(text): Returns the length of a text string.
  • Use Case: Calculate the number of characters in the account description
  • Example: LEN(Account.Description) = LEN(“SalesforceGeek”) = 14
• UPPER(text): Converts a text string to uppercase.
  • Use Case: Converts the product name to uppercase
  • Example: UPPER(Product_Name__c) = UPPER(“cpq”) = “CPQ”
• LOWER(text): Converts a text string to lowercase.
  • Use Case: Converts the email address to lowercase
  • Example: LOWER(Email) = LOWER(JOHN.DOE@salesforcegeek.in) = john.doe@salesforcegeek.in
• TEXT(value): Converts a value to text.
  • Use Case: Converts the close date to a text string
  • Example: TEXT(CloseDate) = TEXT(2025-04-18) = “2025-04-18”
• VALUE(text): Converts a text string to a number.
  • Use Case: Converts the probability field (which might be text) to a number
  • Example: VALUE(Probability) = VALUE(“5”) = 5

IV. Advanced Text Functions

• BEGINS(text, compare_text) – Returns TRUE if text begins with compare_text.
  • Use Case: Checks if the account name starts with “Acme”
  • Example: BEGINS(Account.Name, “Acme”)
• CASESAFEID(id) – Converts a 15-character case-sensitive Salesforce ID to its 18-character case-insensitive equivalent.
  • Use Case: Ensure consistent referencing of Salesforce records in external systems that are case-insensitive.
  • Example: CASESAFEID(Account.Id) = CASESAFEID(“0015g000002MttW”) = “0015g000002MttWAAS”
• TRIM(text) – Removes leading and trailing spaces from a text string.
  • Use Case: Removes any extra spaces before or after the customer name
  • Example: TRIM(Customer_Name__c) = TRIM(”  Salesforce Geek  “) = “Salesforce Geek”
• SUBSTITUTE(text, old_text, new_text) – Replaces all occurrences of old_text in text with new_text.
  • Use Case: Replaces “St.” with “Street” in the address
  • Example: SUBSTITUTE(Address__c, “St.”, “Street”)
• CONTAINS(text, compare_text) – Returns TRUE if text contains compare_text.
  • Use Case: Checks if the comments field contains the word “urgent”
  • Example: CONTAINS(Comments__c, “urgent”)
• FIND(find_text, within_text, start_num): Returns the position of find_text within within_text, starting from an optional start_num.
  • Use Case: Finds the position of the “@” symbol in the email address.
  • Example: FIND(“@”, Email)

V. Display & Picklist Text Functions

• HYPERLINK(url, friendly_name, target): Creates a link to a specified URL with the provided friendly name. The target specifies where to open the link (_blank, _self, _parent, _top).
  • Use Case: Display a clickable link to an external resource directly on a record page.
  • Example:  HYPERLINK(“https://salesforcegeek.in”, “Salesforce Geek”, “_blank”)
• IMAGE(image_url, alternate_text, height, width): Displays an image from the specified URL with optional alternate text and dimensions.
  • Use Case: Embed company logos or status indicators (available in static resources) directly within record details.
  • You can also use Salesforce’s predefined images, for example, “/img/samples/flag_green.gif” etc., in place of static resources.
  • Example:  IMAGE(“/resource/CompanyLogo”, “Company Logo”, 50, 150)
• INCLUDES(multiselect_picklist_field, text_literal): Determines if a multiselect picklist field contains a specified text literal. Returns TRUE if found, FALSE otherwise.
  • Use Case: Check if a Sales Cloud option has been selected in Product_Interests__c multi-select picklist field.
  • Example: INCLUDES(Product_Interests__c, “Sales Cloud”)
• ISPICKVAL(picklist_field, text_literal): Determines if the value of a picklist field is equal to a specified text literal. Returns TRUE if they match, FALSE otherwise.
  • Use Case: Check if Status is Completed.
  • Example: ISPICKVAL(Status__c, “Completed”)
• PICKLISTCOUNT(multiselect_picklist_field): Returns the number of values selected in a multi-select picklist field.
  • Use Case: Count how many options have been chosen in the Product Interests multi-select picklist field.
  • Example: PICKLISTCOUNT(Product_Interests__c)

VI. Date Functions

• DATE(year, month, day): Returns a date value for the specified year, month, and day.
  • Use Case: Creates a date for December 31, 2024
  • Example: DATE(2024, 12, 31)
• DAY(date): Returns the day of the month (1-31) from a date.
  • Use Case: Gets the day of the month from the close date
  • Example: DAY(CloseDate)
• MONTH(date): Returns the month (1-12) from a date.
  • Use Case: Gets the month from the close date
  • Example: MONTH(CloseDate)
• YEAR(date): Returns the year from a date.
  • Use Case: Gets the year from the close date
  • Example: YEAR(CloseDate)
• TODAY(): Returns the current date in the time zone of the user.
  • Use Case: Displays today’s date
  • Example: TODAY()
• DATEVALUE(date_time): Returns the date portion of a date/time value.
  • Use Case: Extracts the date from the creation date and time
  • Example: DATEVALUE(CreatedDate)
• NOW(): Returns the current date and time in the time zone of the user.
  • Use Case: Displays the current date and time
  • Example: NOW()
• DATETIMEVALUE(date_time): Returns a date/time value.
  • Use Case: Returns the last modified date and time
  • Example: DATETIMEVALUE(LastModifiedDate)
• ADDMONTHS(date, num): Returns a date that is the specified number of months before or after a given date.
  • Use Case: Calculate the date three months after the close date
  • Example: ADDMONTHS(CloseDate, 3)
• WEEKDAY(date): Returns the day of the week for the given date, as a number from 1 (Sunday), 2 (Monday) to 7 (Saturday)..
  • Use Case: Determine the day of the week for a specific date
  • Example: WEEKDAY(TODAY())

VII. Time Functions

• TIMEVALUE(date_time): Returns the time portion of a date/time value.
  • Use Case: Extracts the time from the last activity date and time
  • Example: TIMEVALUE(LastActivityDate)
• HOUR(time): Returns the hour (0-23) from a time value.
  • Use Case: Gets the hour of the meeting start time
  • Example: HOUR(TIMEVALUE(Meeting_Start_Time__c))
• MINUTE(time): Returns the minute (0-59) from a time value.
  • Use Case: Gets the minutes of the meeting start time
  • Example: MINUTE(TIMEVALUE(Meeting_Start_Time__c))
• SECOND(time): Returns the second (0-59) from a time value.
  • Use Case: Gets the second of the meeting start time
  • Example: SECOND(TIMEVALUE(Meeting_Start_Time__c))

VIII. Advanced Functions

• CURRENCYRATE(ISOCode) Returns the conversion rate to the corporate currency for the specified ISO currency code. If the currency is the corporate currency, it returns 1.0.
  • Use Case: Convert an amount from a foreign currency to the corporate currency.
  • Example: Assume your corporate currency is USD. You have a currency field ForeignAmount__c in EUR on an object. You want to display the equivalent amount in USD.
  • ForeignAmount__c * CURRENCYRATE(“EUR”) – This output value is in USD.

Salesforce Formula Fields Best Practices

Now that we’ve explored various formula examples, let’s wrap up the blog with some best practices for building effective Salesforce formulas:

• Keep formulas concise and readable.
• Use comments to clarify complex logic.
• Test formulas thoroughly before deployment.
• If possible, avoid creating cross-object formula fields as they may impact the performance.
• Understand formula field limitations, be aware of character limits, supported functions and data types.

FAQ’s

1. Do Salesforce formula fields support history tracking?

No. Formula fields in Salesforce are read-only, and their values are always calculated in real time. If you still need to track those changes as an alternative workaround, you can consider storing the formula field values in another custom field using Flows and enabling field history tracking for that field. 

2. Do formula fields trigger automations?

No, formula fields are read-only and cannot trigger automation or update data. They simply display calculated values in real time.

3. Do Salesforce formula fields update automatically?

Yes. Formula fields are recalculated and updated every time a record is viewed or queried, ensuring you always see the most up-to-date value.

Conclusion

Salesforce Formula Fields are powerful tools that help you automate logic, format data, and enhance record visibility without writing code. By combining operators and functions smartly, you can create dynamic, user-friendly experiences across your org. Keep your formulas clean, test them well, and always follow best practices for optimal performance.

Get a complete Roadmap To Learn Salesforce Admin and Development

Name *

First

Last

Email *

Comment or Message

Submit