--- title: "Commonly used formula functions and formula examples" slug: "commonly-used-formula-functions-and-formula-examples" updated: 2026-04-24T17:17:57Z published: 2026-04-24T17:17:57Z canonical: "help.quickbase.com/commonly-used-formula-functions-and-formula-examples" --- > ## Documentation Index > Fetch the complete documentation index at: https://help.quickbase.com/llms.txt > Use this file to discover all available pages before exploring further. # Commonly used formula functions and formula examples ## Formula examples | You want to | Use field type | Formula | Explanation | | --- | --- | --- | --- | | Multiply 32 and 2.5. | Formula - Numeric | `32 * 2.5` | 32 times 2.5 | | Determine net worth by subtracting liabilities from assets. | Formula - Numeric | `[Assets] – [Liabilities]` | Take the value in the **Assets** field and subtract from it the value in the **Liabilities** field. | | Determine net worth after receiving a standard tax refund. | Formula - Numeric | `([Assets] + 3000) - [Liabilities]` | Take the value in the **Assets** field and add 3000. Then take the result and subtract from it the value in the **Liabilities** field. | | Calculate the area of a circle whose radius is in the field named Radius. | Formula - Numeric | `[Radius] * [Radius] * 3.14159` | Take the value in the **Radius** field and multiply it by the value in the **Radius** field. Then multiply by **3.14159**. | | Calculate the minimum payment. | Formula - Numeric | `Min([Balance Due], 25.00)` | Display whichever amount is less: the value in the **Balance Due** field or 25.00. | | Display a contact's full name. | Formula - Text | `[First Name] & " " & [Last Name]` | Display the value in the **First Name** field. Display a space. Display the value in the **Last Name** field. **Note:** To create a space between the names, this formula inserts a text literal. Quickbase displays whatever characters appear between a set of double quotes. | | Calculate the date one week from today. | Formula - Date | `Today() + Days(7)` | Display the date that is today plus 7 days. | | Discover if net worth of over $1 million. | Formula - Checkbox | `[Net Worth] > 1000000` | Display a**true** result if the value in the **Net Worth** field is greater than 1,000,000. **Note:** This is a *Boolean* statement, which only returns a true or false. This result only applies to a checkbox field which is either ON (true) or OFF (false). | | Find the lesser of two values | Formula - Numeric | `Min(41,23)` | Display whichever amount is less: 41 or 23. The result is 23. | | Find the lesser of two values, where one of those values comes from a field in your Quickbase application | Formula - Numeric | `Min(41,23,[tax])` | Display whichever amount is less: 41, 23, or the value in the **tax** field. | | Total some specific values | Formula - Numeric | `Sum(12.5, 0.5, 3)` | Add 12.5 to .5 to 3 and display the result, which is 16. | | Find the average of a few values | Formula - Numeric | `Average(12, 6, 9)` | Display the average of 12, 6 and 9. The result is 9. | | Find the median of numbers or a duration | Formula - Numeric Formula - Duration | `Median(1, 3, 2)` | Display the median of 1, 3, 2. The result is 2. | | Get today's date | Formula - Date | `Today()` | Display today's date. This function doesn't require any arguments, but you still need to supply the parentheses. | ## Type conversion formulas Use type conversion functions to transfer data from one data type to another. | You want to | Use field type | Formula | Explanation | | --- | --- | --- | --- | | Convert a text string to a date | Formula - Date | `ToDate("Jan 30, 2021")` | Converts "Jan 30, 2021," which is a text literal to a date value | | Convert text to a boolean value | Formula - Checkbox | `ToBoolean("Yes")` | Converts "Yes," which is a text literal to the Boolean value true | | Convert date to text value | Formula - Date Formula Date / Time | `ToFormattedText(Date(2021,01,30),"MMDDYYY")` | Converts a date value to formatted text, in this case 01-30-2021 | | Convert text to a number | Formula - Numeric | `ToNumber("-12.3")` | Converts the text value -12.3 to a numerical value. | | Convert a date field to a work date field | Formula - Work Date | `ToWorkDate([Completion Date])` | Converts the values in the **Completion Date** field to work dates | | Format a date to ISO8601 | Formula - Text | `ToFormattedText(01-01-1970,ISO8601) = 1970-01-01T00:00:00-05:00` | Format 01-01-1970 to ISO8601. ISO8601 is an international standard used in many systems, including our RESTful APIs. | | Format a date to unix time | Formula - Date / time, Formula - Date | `ToUnixTime(01-01-1970) = 18000` | Output the numeric unix time of 01-01-1970. Result will include milliseconds. | ## Setting conditions with the If() function You can set conditions for your formulas. For example, you may want - Total to appear on an invoice only if the order is complete. - **Status** field to say "Completed" only if the **Date Completed** field has been filled out. To do this, use an If() function. When using an If() function: - Describe a condition for Quickbase to evaluate - Specify what to do if the condition is met - Specify what to do if the condition is not met - Separate the condition and arguments from each other with commas This is the basic syntax of an If() function: ```plaintext If(condition, value if condition is true, value if condition is false) ``` ### If() function example - Boolean statement You want your Quickbase app to show companies with a net worth greater than 1 million dollars. Create a Formula - Checkbox field called **Million**, and enter the following formula: ```plaintext If([net worth] > 1000000, TRUE, FALSE) ``` Formula explanation: - If the value in the **net worth** field is greater than 1,000,000 - then turn ON (true) the **Million** checkbox - Otherwise, turn it OFF (false) This is a *Boolean* statement, which only returns True or False. ### If() function example - text field Create a Formula - Text field. Enter the following formula: `If([Net Worth]>1000000, [Telephone Number], "not top sales priority")` Formula explanation: - If the **Net Worth** field is greater than 1,000,000 - then display the value from the **Telephone Number** field - If not, then display the text *not top sales priority*. ### More If() examples | You want to | Use field type | Formula | Explanation | | --- | --- | --- | --- | | Calculate speed. | Formula - Numeric | `If ([Time] > 0, [Distance]/[Time], null)` | If the value in the time field is greater than zero, then display the value in the distance field divided by the value in the time field. Otherwise, leave the field empty. (An empty field is called a null [Link to best practices article].) **Tip:** You don't need to add the null at the end, as Quickbase defaults to a null argument automatically. | | Automatically complete the **Territory** field, based on who the salesperson is. | Formula - Text | `If([Salesperson]=ToUser("baker@example.com"), "Western", "Eastern")` | Take the email address [*baker@example.com*](mailto:baker@example.com) and convert it to the user value connected with that email account (you can use a user name instead of an email address). If the value in the **Salesperson** field is that user, then display the word *Western*, otherwise, display the word *Eastern*. Tip: Form rules can also automatically populate fields based on other values. To set this up for multiple salespeople and territories, use the Case() function instead. Read how in the next section. | | Display an invoice total only if the order is complete. | Formula - Numeric | `If([Order Complete]=TRUE, [SUBTOTAL] + [TAX], null)` | If the **Order Complete** checkbox is on, then add the value in the **subtotal** field to the value in the **tax** field and display it. If not, then leave the field empty (or [null](https://help.quickbase.com/user-assistance/using_formulas_in_QuickBase.html#null)). | | Automatically set the **Status** field to "Complete," when a staff member enters a date in the **Completion Date** field. | Formula - Text | `If(isnull([Completion Date]), "Pending", "Complete")` | If no one's entered a value in the **Completion Date** field (in other words, that field is [null](https://help.quickbase.com/user-assistance/using_formulas_in_QuickBase.html#null)) then display the word *Pending*. If not, display the word *Complete*. | ## Setting multiple conditions with the Case() function Use the Case() function to evaluate multiple conditions against a single field. ### Case() example - turn numeric rating to text - A movie review application contains a field called **Rating.**Viewers pick a number from one to four. - You want to translate this score into a one-word review. - Create a Formula-Text field and use the Case() function to enter multiple conditions: ```plaintext Case([rating],1,"poor" ,2,"fair" ,3,"good" ,4,"fantastic" ) ``` Formula explanation - If the value in the **rating**field is 1, display the word *poor* - If the value in the rating field is 2, display the word *fair*, and so on. **Tip:** Use double slashes (//) to add comments about what each part of your formula does ```plaintext If ( Abs([x]) < 5,        //test the value     "Less than 5",         //return one result     "Not less than 5"      //otherwise return another result ) ``` ## Use the SearchAndReplace function With the`SearchAndReplace `function, you can: - Replace words with other words - Remove words ### Example - empty quotes to remove a word or phrase To filter out noise in your data, like removing HTML tags from email messages that have been synced to a Quickbase app: ```plaintext SearchAndReplace([Email Body],"","") ``` This formula removes all appearances of from the body of the email. ### Example - SearchAndReplace several times in a row Replace several words or phrases in the same piece of text. For example, Create a formula to search the Notes field of an app and replace acronyms with the phrases they represent: `SearchAndReplace(SearchAndReplace(SearchAndReplace([Notes],"GTM","Go-to Market"),"KPI","Key Performance Indicator"),"YoY Growth","Year-over-Year Growth")` ### Example - SearchAndReplace with other functions Use `SearchAndReplace`with other functions like `Left `and `Right` to exclude text in a field. For example, Exclude all text in a field up to the first appearance of the word "Approved." This will help you examine an approval log and see if there are multiple approvals. - The `Left/Right/NotLeft/NotRight `functions only operate on a single character at a time - So, replace a word with a single character - Then use a function such as `NotLeft` - Select a character which does not appear in your source field - For example, "#" doesn't appear anywhere in the source data - Use this `If()`formula to determine if the word "Approved" appears more than once: `If(Contains(NotLeft(SearchAndReplace([Approval Log],"Approved","#"),"#"),"#") = false,true,false)`