# Flow: Formulas that are Commonly Used or Nice To Know

I wish there is a smart formula builder where we can just say “I need a formula in flow that does this and that”, and then “BOOM!” it generates that formula for us automatically. Sadly, it doesn’t exist (or yet?), so it becomes an important mission to familiarize ourselves with formulas. It’s a never-ending journey which requires a lot of research, so in this article, I want to list down all the formulas that I commonly use, and how to combine them to create advanced formulas.

I will constantly update this article. If there are any powerful formulas that you often use but are not here, please share with us!

**Commonly used or Nice to know Formulas**

Here is the official list of all formulas. I will follow a similar categorizing strategy:

- Text Operators
- Logical Functions
- Text Functions
- Number Functions
- Date and Time Functions
- Tips of Formula Building

*** Text Operators**

**& or +**

I want to mention this because it is the easiest yet a very powerful way to create a string. You can use **&** or **+** to connect several strings into one. If you need to add a literal string in between, use **“”** to wrap this literal string. For example, to add an empty space, use **” “**.

**Formula Example**

{!Lead.FirstName}+" "+{!Lead.LastName}

{!Lead.FirstName}&" "&{!Lead.LastName}

**Result**

###### *** Logical Functions**

**IF ( equation , result if true , result if false )**

This has to be the most commonly used formula (as least by me). You enter 3 arguments – first is the expression to be evaluated, then the desired results based on whether the expression is True or False. Sometimes we can even create nested IF formulas.

**Result**

**CASE ( target, scenario 1, result 1 [, scenario 2, result 2] … , general result )**

If you find yourself using too many nested IF, consider CASE() instead. There are three parts for this formula – first is the target that needs to be evaluated. Then two arguments for each scenario. Finally you add a catch-all argument for other scenarios that are not listed.

**Result**

**ISBLANK ( target )**

This returns True or False based on the target you fill in. Use ISBLANK instead of ISNULL.

**Result**

**BLANKVALUE ( target , result if blank )**

If the target is blank, return the specified value. This is the equivalent of IF ( ISBLANK ( target ), result if blank, target).

**Result**

**AND / OR / NOT**

It is nice to know that these are interchangable:

**AND** ( A , B ) = A **&&** B

**OR** ( A , B ) = A** ||** B

**NOT** ( A ) =** !** A

###### *** Text Functions**

**TEXT ( ) / ISPICKVAL ( picklist field , specified value )**

TEXT() is an important function to keep in mind. There are some restrictions of which formula can be used on which data type, so if you bump into this error, try to convert the type into text. Here is worthy to note that TEXT(picklist) = “A” is the same as ISPICKVAL(picklist , “A”).

**BR ( )**

This is just a nice to know formula. If you want to add an empty line in Flow text resources, you can use the BR() function. You treat this as a text, so you can use & or + to connect BR() with other strings.

**Result**

**BEGINS ( )**/**CONTAINS ( )****/ INCLUDES ( )**

Here I want to mention their equivalence in the Decision Element:

**BEGINS() **= **Starts with** operator

**CONTAINS() / INCLUDES() **= **Contains** operator

Also, CONTAINS() does not work for multi-picklist fields. We need to use INCLUDES() instead.

**SUBSTITUTE ( target , text to be replaced , text to replace )**

Replace any characters in your string into other characters! You can also remove the empty spaces inside a string.

**Formula Example**

SUBSTITUTE({!Lead.Email}, "test", "replaced")

SUBSTITUTE( target , " ", "")

**Result**

**TRIM ( target )**

Remove the spaces and tabs from the beginning and end of the target. If you want to remove the spaces **inside** the target, use SUBSTITUTE() instead.

**LEN ( target )**

This returns the number of characters of the target – spaces and symbols are all included. It can also be used to calculate the length of a collection. The length is an important information when we want to work with the index of a string.

Index is the **position** of a specific character in a string. In Apex or other programming language the index starts from 0**, but in Flow** **it starts from 1**. By knowing the length of a string, we will know the range of the index of that string. For example, if the length is 10, we know the index is 0-9, but in Flow it is 1-10.

**Result**

**FIND ( target [start position] ,****character(s) to find**)

This function returns the index of the specific character you want to find. You can search for only one character (ex. @) or a string (ex. com). If you search for a string, it will return the position of **the first character of that string**. You can also specify a start position to skip part of the target string.

It is very handy when we are working with some advanced text formula.

**Result**

**LEFT ( target , number of character to return ) / RIGHT (****target , number of character to return****) / MID (****target , start position , number of character to return****)**

These three are very powerful functions. You can return a specific number of characters from the target string, either starting from the left, the right, or the middle.

For MID(), the start character will be included as well. For example. MID(“Hello”, 2 , 2) will return “el”.

**Result**

**Return Substring**

Combining LEN(), FIND(), LEFT()/RIGHT()/MID(), we can get a specific substring of a string variable. This applies to many use cases:

**Get my org’s url:**

The $Api.Partner_Server_URL_530 variable returns the endpoint of your Salesforce org. This formula is saying “Starting from the left of the entire URL, return the characters all the way until the position of “/services”.

LEFT({!$Api.Partner_Server_URL_530},FIND("/services", {!$Api.Partner_Server_URL_530}))

**Result**

**Get the email domain:**

The previous formula is easy, because the position of a character is the same as “how many characters are in front of this position” . However, since email domain is on the right side, we have to calculate “how many characters are after this position”.

We can achieve that by using **LEN( target ) – FIND ( character to find , target ). **Then we use the RIGHT() function to get the domain.

Include @: RIGHT({!Lead.Email}, LEN({!Lead.Email}) - FIND("@",{!Lead.Email})+ 1) Not Include @: RIGHT({!Lead.Email}, LEN({!Lead.Email}) - FIND("@",{!Lead.Email}))

**Result**

** ***** **Number Functions

*****Number Functions

**VALUE ( )**

Similar to the TEXT() that turns the variables into the text type, VALUE() turns them into the number type.

**CURRENCYRATE ( currency code )**

This is a function that might get forgotten easily, but you can use this to convert the currency fields into the desired currency. It requires two steps – first to divide the conversion rate of the original currency so the value converts to the default currency. Then you multiple it by the desired currency conversion rate.

**Formula Example**

currency field / CURRENCYRATE(original currency code) * CURRENCYRATE(desired currency code)

Ex. To convert values from EUR to NOK: value in EUR / CURRENCYRATE("EUR") * CURRENCYRATE("NOK")

Note: you can usually combine this function with the CurrencyISOCode field: Ex. CURRENCYRATE( TEXT ( {!Lead.CurrencyIsoCode} ) )

**Result**

###### *** Date and Time Functions**

**+ or –**

You can actually use + or – to modify the date. Keep in mind that the unit is always **DAY** (so + 1 is add one day). If you want to add hours or minutes, use 1/24 or 1/1440.

**Result**

**ADDMONTHS ( date , number of months to add )**

But if you want to add/minus months, using +/- might not be accurate since each month has a different length. In that case, use ADDMONTHS() instead. Note that there is no minusMonths() but the number can be **negative**.

**Result**

**DATE ( year , month , day )**

You can enter three numbers and make it into a date.

**Result**

**YEAR ( date ) / MONTH ( date ) / DAY ( date ) …**

You can return the year, month, or day of a date and turn it into a number. These is very helpful together with the DATE() function – Ex. to return the first of current/next year, or the first of current/next month.

**Formula Example**

First of this year: DATE(YEAR(TODAY()),1,1)

First of next year: DATE(YEAR(TODAY())+1,1,1)

First of this month: DATE(YEAR(TODAY()),MONTH(TODAY()),1)

First of next month: IF(MONTH(TODAY()) = 12, DATE(YEAR(TODAY())+1,1,1), DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))

**Result**

**WEEKDAY ( date )**

This returns the number of weekday from a specific date – 1 for Sunday, 2 for Monday,… 7 for Saturday. This is a very important function for the advanced date calculation.

**TODAY ( ) / NOW ( )**

TODAY() returns date and NOW() returns date/time. Alternatively, you can also use the $Flow global variable to get the current date or date/time.

**Result**

**Find Upcoming Friday**

To be honest with you, date-related formulas are my least favorite. It is usually very complex and we have to consider many scenarios. Salesforce even has a whole article to share some examples, and it is just the tip of the iceberg. But I am sharing one formula that I really like – to find the next specific weekday!

**Formula Example**

The concept is quite straightforward – in any week, the difference between two dates will be the same as the difference of their weekdays, so the equation is Date 1 – Date 2 = Weekday (Date 1) – Weekday (Date 2). If we want to know the Friday of the same week, it becomes: Target Date – Today = 6 (Weekday for Friday) – Weekday (Today). Then move all the known numbers to the right:** Target Date = Today + 6 – Weekday(Today)**

Then to think about the upcoming Friday, we have to consider if the Friday this week has passed. If it has, add 7 days to get the Friday next week.

Upcoming Friday: IF(WEEKDAY(TODAY())<=6, TODAY() - WEEKDAY(TODAY()) + 6, TODAY() - WEEKDAY(TODAY()) + 6 + 7)

If you want to find another weekday, simply switch the X in the below formula. (Note: 1 is Sunday)

Upcoming ____day: IF(WEEKDAY(TODAY())<=X, TODAY() - WEEKDAY(TODAY()) + X, TODAY() - WEEKDAY(TODAY()) + X + 7) (X is the number representation of that weekday)

You can also extend this formula to get the Friday after Y weeks by adding Y * 7.

**Result**

**Tips of Formula Building**

Here are some tips of how to build formulas more efficiently:

**1. Build them in a Screen Flow:**

I like to use a Display Text component to check if my formula works properly. This will eliminate many potential errors (ex. wrong indexing) and shorten the troubleshooting time.

**2. Use formula builder from any object** **or a custom component**

Flow Builder is getting better at detecting the errors, but the errors don’t prevent us from saving a formula resource. In this case, I will sometimes go to any object and use the formula builder when creating a new field. It is a nice method when you are just starting with formula (remember not to save that field of course).

Alternatively, you can consider this custom component that enables you to build formula directly in Flow.

**3. Do A LOT of research and testing**

As mentioned in the beginning, it does requires a lot of practice to become proficient in formula building. Try to research and test as much as possible, and note down some good formulas that you have seen (You will thank yourself!). Here is another example sheet from Salesforce. Also, remember there are some formulas we cannot use in Flow.

This is a great resource, Melody! I am not fond for formulas either, and when I get stuck I go to the community. I’m saving this one 😎. Thank you.

Glad that you find this useful Gustavo:)

Thank you for putting this in one place and sharing it with the world!

My pleasure, Lae! Thanks for the nice words😊

Great article, but your documentation on the FIND() function has the attributes backwards.

Thanks for pointing out Trevor! I have now updated it.

This is the most helpful resource I have found for a formula newbie! Thank you.

Thanks so much for the kind words Elissa! I am glad that you find this helpful!

What an incredible resource for reference! Many thanks for sharing!

“I wish there is a smart formula builder…” – what prophetic words! Wondering if you’ve been trying out ChatGPT, and what you think when you ask it to build a formula for you?