Formula Fields

Table of Contents

What is the purpose of a formula?

Our Zoho People's formula fields are as same as like any other advanced custom fields. Using our function type, field type and an operator type, you can build a formula as per your requirement. You also have an option to view all of the function's description, usage and examples for the complete understanding and moreover to make use of these in a right scenario. The administrators can create and make use of these formula expressions for multiple use cases like updating salary details, calculating reimbursements, work experience, etc.,

What are the supported formula field types?

Below are supported field types for formula calculation:

Formula FieldDescription
NumberPositive or Negative integer.
DateDate that represents a day on the calendar. It represents a specific day in the past, present or future.
CurrencyNumber in currency format with a currency sign.
DecimalDecimal value.
Single line fieldIt is a one-line input field where the user can enter the text.
E-mailTo provide e-mail address.
URLTo provide any web URLs

List of operators

Below is the list of all the supported operators.

  • +Add
  • -Subtract
  • *Multiply
  • /Divide
  • %Remainder
  • ()Parentheses.
  • !=Not equal
  • ==Equals
  • >Greater than
  • >=Greater than or equal

Built-in functions

There are four types of built-in functions: Numeric functions, Date and Time Functions, String Functions and Boolean Functions.

Numeric functions: These are used primarily for numeric calculations.

Numeric FunctionsDescription
Abs()This function returns the absolute value of a number. The absolute value of a number is its distance from zero.
Avg()This function calculates the average value of the list of numbers.
Ceil()This function rounds the number up to the nearest integer.
Floor()This function rounds the number down to the nearest integer.
Max()This function returns the highest number from the list of numbers.
Min()This function returns the lowest number from the list of numbers.
Round()This function returns the number after rounding off, if the decimal part is greater than or equal to 0.5, it rounds up the number, if not, it rounds down.
Sqrt()This function returns the positive square root of a given number.
Sum()This function returns the total sum of the list of numbers.
ToNumber()This function converts the argument into a number.

Date and Time Functions: These functions are used for date and time manipulations.

Date and Time FunctionsDescription
AddDate()This function returns the date obtained by adding 'n' (year/day/month/hour/min/sec) to the given date.
CurrentDate()This functions returns a date time value representing the current moment.
DateDiff()This function returns the difference of two dates in the specified format(days/months/years/hours/minutes/seconds). If the former is greater than the latter, the result will be positive else the result will be negative.
GetDate()This function returns the date corresponding to the date time expression.
GetDays()This function returns the number of days in a month corresponding to the given date and time expression.
GetHours()This function returns the hour corresponding to the given date and time expression.
GetMinutes()This function returns the minute corresponding to the given date and time expression.
GetMonths()This function returns the month corresponding to the given date and time expression.
GetSeconds()This function returns the seconds corresponding to the given date and time expression.
GetTime()This function returns the time corresponding to date and time expression.
GetWeekday()This function returns the day of the week (1-7) corresponding to the input date, where 1 is Sunday, 2 is Monday and so on.
GetYears()This function returns the year corresponding to the given date.
Now()The function returns the current date and the formula gets recalculated daily based on current date and time.
SubDate()This function returns the date obtained by subtracting 'n' (year/day/month/hour/Min/sec) from the given date time expression.

String Functions: These functions are used for string manipulations.

String FunctionsDescription
Concat()This method adds two or more strings and returns a new single string.
Contains()This function compares two arguments of text and returns TRUE if the first argument contains the second argument. If not, returns FALSE.
EndsWith()This function calculates TRUE if the string ends with the search string, otherwise returns FALSE.
Equals()This function returns TRUE, if two strings are equal, otherwise returns FALSE.
Find()This function returns the index of the nth occurrence of the search string. If the nth occurrence of the search string is not found, it returns -1.
Lower()This function converts all letters in the specified string to lowercase.
Replace()This function replaces each occurrence of the search string in the input string with the corresponding replace string.
StartWith()This function returns TRUE if the string begins with the search string, otherwise returns FALSE.
SubString()This function returns a portion of an input string, from a start position in the string to end position.
ToString()This function converts the argument to a string.
Trim()This function removes the spaces from the beginning and end of the string.
Upper()This function converts all letters in the specified string to upper case.

Boolean Functions: This function describes how to determine a Boolean value output based on some logical calculation from Boolean inputs.

And()This function returns TRUE if all boolean expressions are TRUE; returns FALSE if any one of the boolean expressions is FALSE.
If()This function returns one of two values, depending on the value of a given logical condition. If the boolean test is TRUE, returns the first generic value, otherwise returns the second generic value.
Not()This function returns the logical negation of the given expression (If the expression is TRUE, returns FALSE).
Or()This function returns TRUE if any one of the boolean expressions is TRUE. Returns FALSE if all boolean expressions are FALSE.

Add formula fields

  1. Log in as administrator.
  2. Click Setup > Forms & Tabs.
  3. Add new form or select an existing form from the forms list.
  4. Drag and drop the Formula field into the section.
  5. Provide Display Name and Label Name.
  6. Click Select Function drop-down list and select the respective type of function according to your need and click Insert.
  7. Click the required field in Select Field and click Insert.
  8. Click respective operator in Select Operator and click Insert.
  9. Click Check Syntax > Done. The formula will get calculated automatically based on the inserted functions, fields and operators.

Edit formula fields

  1. Log in as administrator.
  2. Click Setup > Forms & Tabs.
  3. Select an existing form from the list.
  4. Hover your mouse over the specific formula field and click Edit this field.
  5. Modify the formula field as needed.
  6. Click Done.

Note:

  • The formula will get automatically recalculated when the formula is modified.
  • The Now() function gets updated automatically at the start of the day.

Use cases for formula expressions

Here are just a few use cases with formula fields of how you can use them effectively in your day-to-day HR activities.

To calculate employee's total work experience:

You can calculate your employee's total work experience using below formula fields. You may also use the same formula expression to calculate similar types of different scenarios like age calculation, travel experience etc.,

To calculate employee's payslip:

Using formula fields, you can easily calculate your employee's payslip automatically without doing any manual calculations. Once you enter all the details in the form fields, you will get the net pay off your employee's salary.

To calculate employee's project deadline:

You can send reminders when the employees' project deadline are near to the end date. You can make them aware of the deadline automatically using this following formula expression.

Learn more about automation use cases.