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.

- Pull data from other fields to get a result
- Automate complex calculations
- Create custom formula fields

- Click
- Navigate to
*Customization**> Layouts and Fields > Tasks*. - Click the layout to add a new formula custom field.
- Drag and drop a formula field from the
*New Fields*tray into the layout editor (main panel). - Name the formula field.
- 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.

- You can pass default fields, custom fields, numeric values, date values, text strings, and more as arguments into the function.
- Set return data type for the formula output value. However, you cannot modify the return type once the field is saved to the layout.
- Click
**Add Layout**to include the field in the layout. When you**Cancel**, the field is never stored anywhere.

- You can add up to 10 formula custom fields.
- You
must enclose the text strings and date arguments within single
quotes('). However, you need not enclose the default or custom field
arguments within single quotes (').
- The date and time arguments passed into the function must have this format: mm-dd-yyyy and hh:mm.

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.

- 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.

- The return type of a formula cannot be modified once saved.
- Formula
fields are included in the total number of custom fields. Based on the
return type of the formula, the count of other field types will
decrease. For example, if you create a formula field with single-line
text as return type, then the count of single-line text field type will
decrease.

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.

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.

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.

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.

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.

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.

If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.

Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.

New to Zoho TeamInbox?

New to Zoho ZeptoMail?

New to Zoho DataPrep?

New to Zoho Workerly?

New to Zoho Recruit?

New to Bigin?

New to Zoho CRM?

New to Zoho Projects?

New to Zoho Sprints?

New to Zoho Assist?

# Related Articles

## Timesheet Layouts & Fields

Zoho Projects supports custom fields and layouts for timesheets. Create your own fields and use them for customizing timesheets as per your business requirements. You can also associate a layout while adding time log. Click here to learn more about ...## Task Layouts & Fields

Zoho Projects supports custom fields and layouts for tasks. Create your own fields and task layouts and use them for creating tasks as per your business requirements. Click here to learn more about access privileges for Custom Fields and Task ...## Custom Functions

Automate your issues workflow using Custom Functions. What is a Custom Function? A custom function is a piece of code that can be used for automating a process. You can write your own function in Deluge and call it from one or more business rules. It ...## Is it possible to import the custom fields for issues within a project?

Yes. we do have a option to import the issues along with custom fields. The supported file formats are XLS, XLSX and CSV file.## Save Custom View

Save filter selection a new custom view. Custom view can be saved for own use, for selected users, or for all users. Save Custom Views To save a custom view, select filter options to establish a criteria. Select a project from Recent Projects or the ...