People Help

Formula Fields

This guide explains the basic functions and the usage of formula fields. We suggest you to go through this help page completely before you actually start using them.

What is a formula field?

A formula field is a field type that allows users to perform basic and advanced functions using different formulas. Using a formula field, you can create fields that are calculated instead of being entered by the user.

Formula Fields typically manipulate Number, Decimal and Date fields. The value of this field could be numeric or date depending on the output of the formula. In date manipulations, the result will be given in milliseconds, which you can format as per your requirement. Formula fields get recalculated automatically whenever the formula is changed. It also gets recalculated when a field involved in the formula is changed. Formula fields is same as any other custom field. Users with the 'Admin' role can create formula fields by adding a new field and selecting the formula type.

What are the supported formula field types?

Zoho People supports 4 types of formula fields: Number, Decimal, Date and Currency. Please refer below the tabular column of the formula data types and their description.

Formula Data Type Description
Number Positive or Negative integer.
Date Date that represents a day on the Calendar. It represents a specific day in the past, present or future.
Currency Number in currency format with a currency sign.
Decimal Decimal value.

What are the list of operators?

Below is the list of all the supported operators.

  • Add
  • Subtract
  • Multiply
  • Divide
  • Open and closed parentheses.
  • Current Date
  • Increment Date

How do I add formula fields?

  1. Log in as Admin.
  2. Go to 'Setup' at the top of the page.
  3. Click 'Forms & Tabs'.
  4. Add a new form or clone a form or select an existing form from the forms list.
  5. Drag and drop the 'Formula field' to the form editor area or into the Section.
  6. A pop-up window will appear on your screen.
  7. In the pop-up window, specify the Label name.
  8. The fields which can be used for the formula calculation will be listed at the right side of the formula field window as shown in the image below.
  9. Add the required fields under 'Expression'.
  10. Click 'Done'.
  11. Based on the specified fields, the values will be calculated.

Note:

  • You need to add all the required fields in the form before adding the formula field. Only then, you will be able to select the required field types from the list as shown in the image above.

How do I edit formula fields?

You can edit the formufla field at any time. Please follow the steps below to do so.

  1. Log in as Admin.
  2. Go to 'Setup' at the top of the page.
  3. Click 'Forms & Tabs'.
  4. Select an existing form from the forms list.
  5. Hover your mouse over the specific formula field and click on the 'Edit this field' option as shown in the image below.
  6. Modify the formula field as needed.
  7. Click 'Done'.

Note:

  • The formulas will automatically get recalculated when the formula is modified.

Sample Formula Expressions

Here are just a few examples with detailed code of formula fields of how you can use them in a form. You can use them in multiple different ways to calculate field values, but these are just a few simple examples to give you some ideas.

To Calculate Employee's Total Work Experience

You can use this type of formula field expression to represent a span of time. For example, you can calculate your employee's total work experience using any two date fields. This will result the total time duration. You may also use the same expression to calculate similar types of different fields like Training, Travel days etc.,

(Date of Exit - Date of Joining) / (1000 * 60 * 60 * 24)

To Calculate Employee's Age

Calculating age is almost the same as calculating the experience. The only difference here is you will be adding 365 to the formula just to get the result value in Years. If you miss it out, your age will be calculated in Days.

(Today's Date - Date of Birth) / (1000 * 60 * 60 * 24 * 365)

To Calculate the Date One Week from Today

(Today's Date + Increment Date)

Steps to add the formula field for Increment Date

  1. Log in as Admin.
  2. Go to 'Setup' at the top of the page.
  3. Click 'Forms & Tabs'.
  4. Add a new form or clone a form or select an existing form from the forms list.
  5. Add a new formula field using drag and drop option.
  6. Name the Label.
  7. In the 'Expression' box, click the 'Date icon'.
  8. Today's Date will be displayed in the box.
  9. Then click the Add (+) icon.
  10. Now click the 'Increment Date' icon.
  11. The function '0W:0D:0H:0M:0S' will be displayed.
  12. Change the week as 1 week like '1W:0D:0H:0M:0S'.
  13. Click 'Done'.

To Calculate Employee's Payslip

Using formula fields, you can easily calculate your employees' payslip automatically without doing any manual calculations.

(Basic + HRA + Conveyance + Allowance + Medical Reimbursement) - (PF + Income Tax + Other Deductions)

Once you enter all the details in the form fields, you will get the Net pay of your employee's Salary.

Top