A formula field will automatically calculate and derives its value by referring to the value of the specified fields or the numeric, text, or date values given in the formula. You can customize fields with formulas using functions like numeric, logical, date/time, & text functions. To create a formula, select the functions and pass any argument like numbers, date, time, already existing fields, custom fields, and text strings. You can set return type for every formula based on the function and arguments passed into the function. Also, you can use numeric and logic operators to create a formula. 

Benefits:

1. Pull data from other fields to get a result
   
2. Automate complex calculations
   
3. Create custom formula fields
   

Create Formula Custom Field

1. Click    in the upper-right corner of the top navigation bar.
   
2. Navigate to Customization > Layouts and Fields > Tasks.
   
3. Click the layout to add a new formula custom field.
   
4. Drag and drop a formula field from the New Fields tray into the layout editor (main panel).
   
5. Name the formula field.
   
6. Select a function to create formula. Click here to view the list of functions and operators. 
   
   • You can pass default fields, custom fields, numeric values, date values, text strings, and more as arguments into the function. 
     
   • You can use the numeric and logical operators in an expression of the formula.
     
7. Set return data type for the formula output value. However, you cannot modify the return type once the field is saved to the layout. 
   
8. Click Add Layout to include the field in the layout. When you Cancel, the field is never stored anywhere.
   

The return type of a formula determines the type of data that you want to be returned from the formula.

• Single-line text: Returns a text string of up to 255 characters.
  
• Multi-line text: Returns a text string of up to 4000 characters.
  
• Integer: Returns a positive or negative integer of up to 18 digits.
  
• Decimal: Returns a decimal value of up to 18 digits.
  
• Currency: Returns the currency type that you have set when creating the formula field. 
  
• Percent: Returns a number in percent format of up to 18 digits followed by a percent sign.
  
• Day: Returns the output as days based on the Business Hours you have configured in the portal settings.
  
  • For example, if Business hours are set as 8 hours per day, and the formula is {duration}+10 where duration is 6 hours, the output will be 16 hours. However, the return type is “Day” and hence the output 16 hours will be converted to 2 business days based on business hours. Similarly, if your input is in minutes format, it will be converted to hours and then to days. 
    
• Date/time: Returns data that represents the date and time in the mm-dd-yyyy and hh:mm formats. If you want to return the current date and time in a formula, use the NOW() function. ​
  

Text Functions

Joins the elements of an array along with a separator.

Example: ARRAYJOIN([{EMPLOYEE ID},{WORK_IN_HRS}],'-') ​

In this example, a list of values is referred to and displayed as an array. The value of the Emp ID is Zyl01 and the value of work hours is 5, hence the output will be Zyl01-5. 

Emp ID is a custom field. Its value will be referred to calculate the output. You can replace the values with custom fields, numeric values, or expressions.

Joins multiple values or expressions into one. If you want to concatenate text strings, you must enclose them within single quotes.

Example: CONCATENATE({FEATURE},'-',{SCOPE}) ​

In this example, multiple custom fields are referred to in a single field and their values are concatenated. Similarly, you can refer to the custom fields or values which you want to display in a single field. 

Feature and Scope are custom fields. Their values will be referred to calculate the output. You can replace the values with other custom fields, text strings, numeric values, date/time arguments, or other expressions.

Returns the number of characters in the given text string.

Example: LEN({DESIGN}) ​

In this example, the character count including blank space in the text field “DESIGN” will be identified. If the DESIGN field contains the text “New Design to be implemented”, the output will be 28.

Design is a custom field. Its value will be referred to calculate the output. You can replace the values with text strings or text custom fields.

Logical Operators

Evaluates if the given value is greater than the other value in a logical expression. Returns true when logic satisfies, otherwise, returns false.

Example: {REVENUE} > {EXPENSE}​

Revenue and Expense are custom fields. Their value will be referred to calculate the output. You can replace the values with custom numeric fields, numeric values, or expressions.

Evaluates if the given value is lesser than the other value in a logical expression. Returns true when logic satisfies, otherwise, returns false.

Example: {PERCENTAGE COMPLETION} < 80​

Percentage completion is a custom field. Its values will be referred to calculate the output. You can replace the values with custom numeric fields, numeric values, or expressions.

Evaluates if the given value is greater than or equal to the other value in a logical expression. Returns true when logic satisfies, otherwise, returns false.

Example: {PROFIT} >= 10000​

Profit is a custom field. Its values will be referred to calculate the output. You can replace the values with custom numeric fields, numeric values, or expressions.

Evaluates if the given value is lesser than or equal to the other value in a logical expression. Returns true when logic satisfies, otherwise, returns false.

Example: {TIME} <= 15​

TIME is a custom field. Its values will be referred to calculate the output. You can replace the values with custom fields, numeric values, or expressions.

Evaluates if the given value is equal to the other value in a logical expression. Returns true as output when logic satisfies, otherwise, returns false.

Example: {COMMISSION} == 15000​

Commission is a custom field. Its values will be referred to calculate the output. You can replace the values with custom fields, numeric values, or expressions.

Evaluates if the given value is not equal to the other value in a logical expression. Returns true when logic satisfies, otherwise, returns false.

Example: {WORK_IN_HRS} != 5​

With this formula, you can calculate the cost per person. If the values of the fields Total cost and People count are 10000 and 20 respectively, then cost per person is 500.

Duration is a custom field. Its values will be referred to calculate the output. You can replace the values with custom fields, numeric values, or expressions.

Logical Functions

Evaluates if all the given arguments are true.

Example: AND({PERCENTAGE COMPLETE}<=90,{PERCENTAGE COMPLETE}>=50) ​

With this formula, you can identify if the percentage completion is between 50 and 90 percent. When the percentage completion value is greater than or equal to 50 and less than or equal to 90 and the output will be True. Otherwise, the output will be False. 

Percentage Complete is a default field. Their values will be referred to calculate the output. You can replace the values with custom fields, numeric values, or other expressions.

Evaluates if at least one of given two values or parameters is true.

Example: OR({PERCENT}>50,{REVENUE}>{EXPENSE}) ​

With this formula, you can identify whether a deal is qualified. When either percent value is greater than 50 or revenue is greater than expense, the output will be True. Otherwise, the output will be False.

Percent, Revenue, and Expense are custom fields. Their values will be referred to calculate the output. You can replace the values with custom fields, numeric values, or expressions.

Evaluates the logic and returns value1 if true or else returns value2.

Example: IF({PLANNED COST}>{ACTUAL COST},'OVERRUN','SURPLUS') ​

With this formula, you can identify if the budget is overrun or surplus. If the value of Planned cost is greater than Actual Cost, the output will be Overrun, otherwise, the output will be Disqualified.

Percent is a custom field. Its value will be referred to calculate the output. You can replace the values with custom fields, numeric values, or expressions.

You can also nest the IF functions.

Example: IF({COMMISSION}>10, '$150', IF({COMMISSION}<=7, '$70', '$110)) ​ 

In this example, if the commission is greater than 10, the output is $150, otherwise the nested IF function will be evaluated. 

Numeric Operators

Adds the given numeric values or field parameters.

Example: {MATERIAL COST} + {ACTUAL COST}​

With this example, you can create a formula field named “Total cost” with the above formula. If the value of the field Material Cost is 2000 and Actual Cost is 3000, then the output will be 5000.

Material cost and Actual cost are custom field. Their value will be referred to calculate the output. You can replace the values with custom numeric fields, numeric values, or expressions.

Subtracts the given numeric values or field parameters.

Example: {REVENUE} - {EXPENSE}​

With this formula you can calculate profit. If the values of the fields Revenue and Expense are 8000 and 5000 respectively, then profit is 3000.

Revenue and Expense are custom fields. Their values will be referred to calculate the output. You can replace the values with custom numeric fields, numeric values, or expressions.

Multiplies the given numeric values or field parameters.

Example: {COST PER UNIT} * {QUANTITY}​

With this formula you can find the total cost. If the values of the fields Cost per unit and Quantity are 200 and 80 respectively, then the total cost will be 16000.

Cost per unit and Quantity are custom fields. Their values will be referred to calculate the output. You can replace the values with custom numeric fields, numeric values, or expressions.

Divides the given numeric values or field parameters.

Example: {TOTAL COST} / {PEOPLE COUNT}​

With this formula you can calculate the cost per person. If the values of the fields Total cost and People count are 10000 and 20 respectively, then cost per person is 500.

Total cost and People count are custom fields. Their values will be referred to calculate the output. You can replace the values with custom fields, numeric values, or expressions.

Numeric Functions

Returns the average of the given set of numbers or field parameter.

Example: AVERAGE({PHASE1},{PHASE2},{PHASE3}) ​

With this formula, you can calculate the average of different phase values. If the values of the fields phase1, phase2, and phase3 are 9000, 8000, and 10000 respectively, then the average phase value is 9000. 

Phase1, Phase2, and Phase3 are custom fields. Their values will be referred to calculate the output. You can replace the values with custom fields, numeric values, or expressions.

Returns the number of given numeric values or fields only. Other text strings or characters will not be counted. 

Example: COUNT(1,2,3,'','FOUR') ​

With this formula, you can count the numeric values or number field parameters. However, text string or blank space will not be counted. You can replace the values with custom numeric fields or numeric values.

Returns the integer that is less than or equal to the specified value.

Example: INT({PROFIT}) ​

With this formula, you can find the profit value round off to a value less than or equal to itself. If the value of the field profit is 89.48, then the output will be 89.

Returns the largest of the given set of numbers or numeric fields.

Example: MAX(0.06*{COST PRICE},100) ​

With this formula you can identify the largest commission value. If the value of the field Cost price is 2000, then 0.06*2000 is 120 which is greater than the minimum value 100. The commission value is 120.

Returns the smallest of the given numbers or numeric fiels.

Example: MIN({PHASE1},{PHASE2}) ​

With this formula, you can find the lowest phase value. If the value of the fields phase1 and phase2 are 2500 and 3000 respectively, the lowest phase value is 2500.

Returns the remainder by dividing the value by the divisor.

Example: MOD({TOTAL UNITS},{PEOPLE COUNT}) ​

With this formula, you can find the remaining units available. If the value of the fields Total units and People count are 5000 and 2300 respectively, the remaining units are 400.

Returns the integer nearest to the specified value, constraining the new value as per the given precision.

Example: ROUND({PRICE} * {DISCOUNT}, 2) ​

With this formula, you can calculate the discounted price rounded off to the nearest value and decimal places constrained based on precision. If the value of the fields Price and Discount are 999 and 0.07 respectively, the output of Discounted price is 69.93.

Calculates the sum of the given values or field parameters.

Example: SUM({LIABILITIES},{EQUITY}) ​

With this formula, you can calculate the value of the assets. If the value of the fields Liabilities and Equity are 100000 and 10000 respectively, the value of the assets is 110000.

Date and Time Functions

Returns the difference between the two specified dates in the given units.

Example: DATETIME_DIFF({DATE},TODAY(),'DAYS') ​

With this formula, you can find the difference between two given dates. If the given date is 07-15-2019, today's date is 07-20-2019, and the specified unit is "days", the output will be 5 days. To pass a date value as an argument, you must enclose them within single quotes. Supported date format is mm-dd-yyyy.

Returns the day value of the given date parameter as a number from 1 through 31.

Example: DAY({COMPLETION DATE}) ​

With this formula, you can find the day value of the given date. If the given date is 07-15-2019, the output will be 15. To pass a date value as an argument, you must enclose them within single quotes. Supported date format is mm-dd-yyyy.

Returns the hour value of the given date parameter as a number from 0 (12:00 am) through 23 (11:00 pm).

Example: HOUR('07-12-2019 12:45 PM') ​

To pass a date/time value as an argument, you must enclose them within single quotes. Supported date and time format is mm-dd-yyyy and hh:mm. 

Returns the minute value of the given date parameter as a number from 0 through 59.

Example: MINUTE('07-12-2019 12:45 PM') ​

To pass a date/time value as an argument, you must enclose them within single quotes. Supported date and time format is mm-dd-yyyy and hh:mm. 

Returns the month of the given date as a number from 1 through 12.

Example: MONTH('07-23-2019') ​

To pass a date value as an argument, you must enclose them within single quotes. Supported date format is mm-dd-yyyy.

Returns the current date and time.

Example: NOW()​

Returns today’s date and time as 12.00 am.

Example: TODAY()​

Calculates the number of days between the current date and another date.

Example: FROMNOW({START DATE})​

If the current date is 07-20-2019 and Start date is 07-15-2019, the output will be 5 days. To pass a date value as an argument, you must enclose them within single quotes. Supported date format is mm-dd-yyyy.

Returns the current week value of the given date parameter.

Example: WEEKNUM({DUE DATE})​

If the Due date is 07-15-2019, the output week number will be 29. To pass a date value as an argument, you must enclose them within single quotes. Supported date format is mm-dd-yyyy.

Returns the year of the given date parameter.

Example: YEAR({COMPLETED DATE})​

If the Completed date is 07-15-2019, the output will be 2019. To pass a date value as an argument, you must enclose them within single quotes. Supported date format is mm-dd-yyyy.