Loading

Formula FAQ

게시 일자: Apr 3, 2026
상세 설명
Answers to some of the most frequently asked questions regarding the 'Formula' feature.
솔루션

Frequently Asked Questions
 

_________________________________________________________________________________________


Q: I am referencing picklist values in my formula but it doesn’t seem to take them/some into account

A: Make sure you reference the API name of the picklist values in your formula. They can sometimes be different from their labels. 

For example, you could have a value with the label 'Other' and the API name set to 'XOther'. A simple IF formula would be:

IF(ISPICKVAL(Picklist__c,'XOther', result if true, result if false)
 

Q: I do not want my validation rule to trigger for a specific profile/role/user

A: First, the $ sign references the logged-in user.

You can now go with the following:

Profile: $Profile.Name<>’System Administrator’

Role: $UserRole.DeveloperName<>'CEO'

User: $User.Alias<>'XXX'

Another way to exclude specific users would be to first create a Custom Permission (let’s call it 'ValidationBypass'), then create a Permission Set and assign this custom permission to it.

You can now assign the Permission Set to the selected users. The only thing left to do is to add this to your validation rule formula:

AND(NOT($Permission.ValidationBypass), [Rest of your formula here without the square brackets])

 

Q: I am referencing the $Profile.Name (or $UserRole.Name) to exclude it in my validation rule, but it doesn’t seem to be bypassed for my non-English-speaking colleagues

A: The name is probably translated in the other language and therefore it is not equal to the name you are referencing in your rule. For example, ‘System Administrator’ in English becomes ‘Administrateur système’ in French and a rule with $Profile.Name<>’System Administrator’ would not work in France.

If using a role, make sure you use the DeveloperName. Unfortunately, it is not available for profiles, you can only use the Name. In this instance, you could use $Profile.ID and hard-code the ID in the formula, but you’d need to make sure the ID is changed if needed when deploying to another environment. A better solution would be to create a Custom Permission that you would assign to the profile and exclude the permission in your validation rule:

AND(NOT($Permission.ValidationBypass), [Rest of your formula here without the square brackets])

 

  Q: I want to calculate the difference between 2 dates and exclude weekends

A: You can use the following formula as a template:

  /*Calculate the number of days between the 2 dates*/

(EndDate__c   - StartDate__c)

/*Remove the number of weekend days*/

-

(FLOOR((EndDate__c   - StartDate__c)/7)*2

+

IF(AND(WEEKDAY(StartDate__c )=1, WEEKDAY(EndDate__c  )<>7),1,

IF(CASE(WEEKDAY(StartDate__c ),1,8,WEEKDAY(StartDate__c ))>CASE(WEEKDAY(EndDate__c  ),1,8,WEEKDAY(EndDate__c  )),2,

IF(OR (WEEKDAY(EndDate__c   )=7, WEEKDAY(StartDate__c )=1),1,

IF(OR (WEEKDAY(EndDate__c   )=1, WEEKDAY(StartDate__c )=7),2,

0)))))

 

Q: I am trying to find the last day of the month of a date field

A: Please use this formula:

ADDMONTHS(DATE(YEAR(DateField__c), MONTH(DateField__c),1),1)-1
The trick here is to take the 1st of the month and year of the date field, add 1 month to it and subtract 1 day.
For example, if my date field is March 3rd 2022, the formula will add 1 month to March 1st 2022 and will return April 1st 2022. We then subtract 1 day to get March 31st 2022

 

Q: How do I know if a date field is in a leap year?

A: Here’s a simple IF formula that you can use as an example:

IF(DAY(DATE(YEAR(DateField__c),3,1)-1)=29, 'Leap year', 'Non-leap year')
We just take the day before March 1st of the year of the date field (last day of February for this same year) and check if it returns 29, in which case, it’s a leap year.

 

Q: When checking if a field is blank/null, my formula doesn’t seem to return the right result, why is that?

A: If your formula references any number, currency, or percent fields, make sure you select the 'Treat blank fields as blanks' option in the 'Blank Field Handling' section of your formula.

Otherwise, a blank field is considered to have a 0 in it and therefore is not considered blank 

 

Q: When trying to save my formula, I get an error that says 'Formula result is data type (Text), incompatible with expected data type (true or false)' or a similar message

A: Make sure you select the right 'Formula Return Type' for your formula. In the example in the question above, it would mean that your return type is set to 'Checkbox', but the formula results in text, therefore you need to select 'Text' as the 'Formula Return Type'

 

Q: I have a simple validation rule in a screen flow on a field. I copied it from a validation rule on an object, but it seems to act the exact opposite way. Why is that?

A: A validation rule in the Salesforce UI displays an error to the user when the result of a formula returns true. 

However, the validation formula in a screen flow needs to return false for the error message to show.

For example, if you don’t want to be able to enter more than 500 in a number field, a 'standard' validation rule would be:

NumberField__c>500

Whereas the validation in your flow should be: 

{!Number}<=500

 

Q: Why is it that when I am referencing a date/time in a formula, it doesn’t return the right date (and time)?

A: Formulas on date and date/time fields return their value in the GMT timezone. You will need to offset the result by the difference in hours from GMT. For example, if you are in New York:

DateTimeField__c+5/24 

However, since time zones can be affected by the DST change and their start and end dates are different each year, it is difficult to manage in a formula.

 

Q: When creating a row level formula in a report, I cannot find a specific field in the left-hand-side panel

A: Please see the article on row-level formula limitations:

https://help.salesforce.com/s/articleView?id=sf.reports_formulas_row_level_limits.htm&type=5

 

Some items are not supported in row-level formulas:

  • Row-level formulas can't reference:
    • Bucket fields
    • Summary formulas
    • Other row-level formulas
  • Row-level formulas don't support these field types:
    • Boolean
    • Timeonly
    • Email
    • Multiselect picklist
  • Some date fields aren't supported, including:
    • Due Date
    • Birthdate
  • Some text fields aren't supported, including:
    • Billing Address

Q: I do not see the option to create a row-level formula in my report

A: Currently, only one row-level formula is allowed per report

Also, please see the article on row-level formula limitations:

https://help.salesforce.com/s/articleView?id=sf.reports_formulas_row_level_limits.htm&type=5

  • "With or Without" custom report types (like Accounts with or without Contacts) don't support row-level formulas. The Add Row-Level Formula button doesn't appear.
  • You can create row-level formulas on historical trend reports, but you can't reference historical fields in row-level formulas.
  • Reporting snapshots don't support row-level formula fields.
  • Row-level formulas aren't available on joined reports. To convert a report with row-level formulas into a joined report, first remove the row-level formulas.
  • The embedded Salesforce Classic report builder in Lightning Experience doesn't support row-level formulas. If a report has a row-level formula, then the Edit (Salesforce Classic) button doesn't appear.
  • You can't create, edit, or delete row-level formulas from the Fields panel. Create, edit, or delete them from the OUTLINE panel.
  • Row-level formulas aren't available in Salesforce Classic. You can't run or edit reports with row-level formulas in Salesforce Classic.

 

Q: I am sure I have the right number of parentheses in my formula (I counted them), but I still receive this error message. Why is that?

Error: Syntax error. Missing ')'

A: If you are 100% sure you have the right number of brackets, this error usually means there is a comma missing. Take a look at this formula:

CASE(Number__c,1,"one",2,"two",3,"three",4,"four",5,"five",6,"six",7,"seven",8,"eight",9,"nine" NULL)

There is a comma missing between 'nine' and NULL at the end and it will return the same error when trying to save it. It would need to be changed to:

CASE(Number__c,1,"one",2,"two",3,"three",4,"four",5,"five",6,"six",7,"seven",8,"eight",9,"nine", NULL)

 

Q: My number formula does not show anything or returns #Error

A: This usually happens when you are dividing by 0. Prevent division by zero errors by including an IF function that determines if the value of a field is zero. For example:

IF(Field__c =0,0, 25/Field__c)

 

There could be other reasons. Please see:

https://help.salesforce.com/s/articleView?id=sf.fixing_formula_errors.htm

 

Q: How do I make more than one field mandatory in a validation rule?

A: You will need to use an OR statement with ISBLANK like so:

OR(

ISBLANK(Field1__c),

ISBLANK(Field2__c),

ISBLANK(Field3__c)

)

 

Bear in mind that if you’re referencing a picklist field, you will also need the TEXT function like so:

OR(

ISBLANK(Field1__c),

ISBLANK(Field2__c),

ISBLANK(Field3__c),

ISBLANK(TEXT(Picklist__c))

)

 

Q: I am trying to validate that a checkbox is blank, but the rule never seems to trigger. Why?

A: A checkbox can never be blank. It has 2 states only, true or false. If you are trying to throw an error when a checkbox is not selected (false), go with:

NOT(Checkbox__c) 

You can also write it like this:

Checkbox__c=FALSE

 

If you want to validate when it is selected (true), simply reference the field itself:

Checkbox__c 

You can also write it like this:

Checkbox__c=TRUE

 

Q: Why is it that my validation rule that checks if a field has changed seems to work fine when I edit the record but not on creation?

A: The ISCHANGED function is not considered on newly created records since there is no actual change to the field (it didn’t even exist before the first save). You may need to add an extra condition with the ISNEW() function or remove the ISCHANGED altogether, depending on your requirements

 

Q: Why doesn’t my automation trigger when I have a simple entry requirement that states that a formula must be equal to X?

A: Formula fields cannot trigger automations (flows, code, etc). One thing you might be able to do is to use the same formula as the field in your entry/trigger condition

 

Q: I am trying to reference a field on the record in the formula for the default value of a field, but it is not available

A: Default values are only available on creation of a record. At that point, no field has any value until the record is saved, hence why you cannot reference any field on the record itself.

You could use a record-triggered flow to populate the field, but this would only show after saving, or you could use a Quick Action and add a predefined value for this specific field

 

Q: Why is it that when hard-coding an ID in a formula, it isn’t taken into account?

A:  There could be a couple of reasons:

1. You are using the wrong record ID

2. Formula fields and validation rules take the 15 character ID, whereas flows use the 18 character ID. You can get the ID of a record in its URL as per this article:

https://help.salesforce.com/s/articleView?id=000324087&type=1

You can convert Ids from 15 to 18 characters here:

https://www.adminbooster.com/tool/15to18

3. Also, some URLs append “2F” in front of the ID, make sure it is not in your formula

 

Q: My formula has a mixture of AND, OR, && and ||. It doesn’t seem to always work

A: You should not mix these as it can “break” the formula

Make sure you either use 

  • AND and OR within the same formula 
  • && and || within the same formula

 

Q: I created a formula that is so complex, it will be hard to understand for anyone else but me. How can I make it more “understandable”?

A: You can add comments to a formula like so:

/*Your comment here*/

Also, if calculating with numbers, make sure you use parentheses. Even though there is an order of execution as to which operation gets performed first, by using brackets, it becomes clearer.

Also, follow this great article:

https://help.salesforce.com/s/articleView?id=sf.customize_formula_best_practices.htm

 

Q: Where can I learn more about formulas?
A:Trailhead is a free way to learn Salesforce formulas

https://trailhead.salesforce.com/


Knowledge 기사 번호

000392562

 
로드 중
Salesforce Help | Article