Formula field | Zoho Sprints Help

Formula field

Formula fields are automatically calculated fields whose value is derived from a formula or expression. They allow you to define fields that can populate dynamically calculated data based on values returned from other standard or custom fields. 

Info
Availability: Premier and Enterprise plan
 
A formula contains one or many of the following:
 
Arithmetic and Logical Operations: Perform addition, subtraction, multiplication, division, or comparing items.
Numbers
Text strings
Function Calls - Perform specific calculations on the provided data to return a result.
Field Access - Retrieve the value of a specific field. For example, created time of work item etc.,.
Conditional ("IF") Expressions - Calculate and return different results based on a specified set of criteria.
 
We have the following Return Data Types, 
  1. Number
  2. String
  3. Date
  4. DateTime
  5. Boolean

Numeric Formula Field  

A numeric function in formula fields lets you perform mathematical operations by using numeric data types.
 
The following table will help you understand the types of arguments required by numeric functions and the type of syntax to form.
 
Function Name
Description
            Syntax
Return Type
            Example
Abs
Returns the absolute value of a number
Abs(Number)
Number
Abs(-42) = 42
Abs(23.2) = 23.2
Ceil
Returns the smallest integer greater than or equal to the input.
Ceil(Number)
Number
Ceil(3.4) = 4
Ceil(-3.4) = -3
Floor
Returns the largest integer less than or equal to the input.
Floor(Number)
Number
Floor(3.4) = 3
Floor(-3.4) = --4
Max
Returns the maximum value from the specified list of numbers.
Max(Number1, Number2, Number3, ...)
Number
Max(1,2,5,4) = 5
Max(1,-6,5,-4) = 5
Min
Returns the minimum value from the specified list of numbers.
Min(Number1, Number2, Number3, ...)
Number
Min(3,1,5,8) = 1
Min(0,-4,-3,1) = -4
Sqrt
Returns the square root of a number.
Sqrt(Number)
Number
Sqrt(9) = 3
Sqrt(16) = 4
 
ToNumber
Converts any string that consists of only numbers into a numeric variable.
ToNumber(generic)

Note: If an invalid number is provided it will return 0.		Number
ToNumber('1000') = 1000
ToNumber('1.32') = 1.32
Count
Returns the count of of numeric values passed as arguments.
Count(generic1, generic2, generic3, ...)
Number
Count('1','2',String) = 2
Count(1,'2','star','abc1') = 2
Average
Returns the average of the values passed as arguments.
Average(Number1, Number2, Number3, ...)
Number
Average(2,4,6) = 4
Average(2,5,7,9) = 5.75
Mod
Returns the remainder of a number after division.
Mod(dividend[Number], divisor[Number])
Number
Mod(10,3) = 1
Mod(12,3) = 0
Round
Returns a number rounded to given number of digits.
Round(Number, noOfDigits[Number])
Number
Round(10.968,2) = 10.97
Sum
Returns the sum of the values passed as arguments.
Sum(Number1, Number2, Number3, ...)
Number
Sum(1,5,6) = 12
 
 
 
 
 
 
To create a numeric formula field,    
  1. Drag and drop the Formula custom field
  2. Enter the name of your field.
  3. Choose a return type.
  4. Choose Numeric in the All Functions drop-down.
  5. You can either enter your own formula expression or create your expression using the provided fields.
  6. Choose the privacy preferences and click Create .

String Formula Field  

A string is an array of characters and can contain space, numbers, symbols or values within quotes to differentiate it from a number or a name.
 
The following table will help you understand the types of arguments required by string functions and the type of syntax to form.
     
Function Name Description  Syntax  Return  Type  Example
Strlen
Returns the length of the string.
Strlen(String)
Number
Strlen('test') = 4
Find
Returns the search letter's position within the string.
Find(String, searchString[String], startPosition[Number]**)
 
Note:
startPosition - startsWith 1.
Number
Find('greenery','n',1) = 5
Find('greenery','e',5) = 6
Concat
Returns the concatenation of all the string params.
Concat(String1, String2, ...)
String
Concat('Zoho',' ','Sprints') =     'Zoho Sprints'
Contains
Returns 'true' if and only if this string contains the search string. Otherwise returns 'false'.
Contains(String, searchString[String])
Boolean
Contains('abcdef','cd') = true
Contains('abcdef','jk') = false
Startswith
Returns 'true' if the string begins with the search string, otherwise it returns 'false'.
Startswith(String, searchString[String])
Boolean
Startswith('abcdef','ab') = true
Startswith('abcdef','jk') = false
Endswith
Returns 'true' if the string ends with the search string, otherwise it returns 'false'.
Endswith(String, searchString[String])
Boolean
Endswith('abcdef','ab') = true
Endswith('abcdef','jk') = false
Tolower
Converts all letters in the input string to lowercase.
Tolower(String)
String
Tolower('APPLE') = 'apple'
Tolower('Apple') = 'apple'
Toupper
Converts all letters in the input string to uppercase.
Toupper(String)
String
Toupper('Apple') = 'APPLE'
Toupper('apple') = 'APPLE'
Trim
Removes the leading and trailing spaces from the input string.
Trim(String)
String
Trim(' abcd ') = 'abcd'
Trim(' ab cd ') = 'ab cd'
SubString
Returns a substring of the input string, from the specified beginIndex and endIndex.
SubString(String, beginIndex[Number], endIndex[Number]**)
 
Note:
beginIndex - startsWith 1.
String
SubString('abcdefg',4,7) = 'defg'
Replace
Replaces each occurrence of the search string in the input string with the replacement string.
Replace(String, SearchString[String], replacementString[String])
String
Replace( 'abcdefg' , 'abc' , 'xyz' ) = 'xyzdefg'
Replace( 'abcdefg' , 'def' , 'xyz' ) = 'abcxyzg'
Strsplit
Split the string based on the given separator and return nth (returnIndex) portion of the string.
Strsplit(String, returnIndex[Number], separatorToSplit[character]**)

Note: separatorToSplit = ' ' is defaultValue
returnIndex - startsWith 1.		Strsplit
Strsplit('abc def ghi',2,' ') = 'def'
ToString
Converts any argument into string.
ToString(generic)
String
ToString(1.2) = '1.2'
ToString(true) = 'true'
 
To create a string formula field,
  1. Drag and drop the Formula custom field
  2. Enter the name of your field.
  3. Choose a return type.
  4. Choose String in the All Functions drop-down.
  5. You can either enter your own formula expression or create your expression using the provided fields.
  6. Choose the privacy preferences and click Create.

DateTime Formula Field  

DateTime functions can be used in formula fields to retrieve the  date and time information in a specific format.
 
The following table will help you understand the types of arguments required by numeric functions and the type of syntax to form.
 
 Function Name  Description  Syntax Return Type  Example

Newdate

Creates a date from the year, month, day, and time params.

Newdate(year[Number], month[Number], day[Number], hour[Number], minute[Number], AM/PM**)

 

DateTime, Date

Newdate( 2021,12 ,21, 06 ,30 ,'AM' ) = 21/12/2021 06:30 AM

Datepart

Returns the date of the DateTime expression.

Datepart(DateTime)

String

Datepart( Newdate( 2021 ,12 ,21 ,06 ,30 ,'AM' ) )  = '21/12/2007 '

Timepart

Returns the time from the DateTime expression.

Timepart(DateTime)

String

Timepart( Newdate( 2021 ,12 ,21 ,06 ,30 ,'AM' ) )  = '6:30 AM'

Adddate

Returns the date by adding n (year/day/month/hour/min) to the given date.

Adddate(DateTime, NumberToAdd[Number], String)

 

Note: String - 'YEAR' or 'MONTH' or 'DAY' or 'HOUR' or 'MIN' (case-sensitive).

DateTime, Date

Adddate( Newdate( 2021 ,12 ,21 ,06 ,30 ,'AM' ) ,2 ,'YEAR' ) = 21/12/2023 06:30 AM

Adddate( Newdate( 2021 ,9 ,21 ,06 ,30 ,'AM' ) ,2 ,'MONTH' ) = 21/11/2021 06:30 AM

Subdate

Returns the date by subtracting n (year/day/month/hour/min) from the given date.

Subdate (DateTime, NumberToSubtract[Number], String)

 

Note: String - 'YEAR' or 'MONTH' or 'DAY' or 'HOUR' or 'MIN' (case-sensitive).

DateTime, Date

Subdate ( Newdate( 2021 ,12 ,21 ,06 ,30 ,'AM' ) ,2 ,'YEAR' ) = 21/12/2019 06:30 AM

Subdate ( Newdate( 2021 ,9 ,21 ,06 ,30 ,'AM' ) ,2 ,'MONTH' ) = 21/7/2021 06:30 AM

Datecomp

Returns the difference in time ( milliseconds) between two days. If the first date is greater than the second date, the result is positive. Otherwise the result is negative.

Datecomp( DateTime, DateTime )

Number (Time difference in milliseconds)

Datecomp( Newdate(2021 ,05 ,19 ,11 , 30 ,'AM') , Newdate(2021 ,05 ,19 ,11 ,00 , 'AM') ) = 1800000

Dayofmonth

Returns the number corresponding to day of the month for the given date.

Dayofmonth( DateTime )

Number

Dayofmonth(Newdate( 2021,05,19,11,30,'AM')) = 19

Hour

Returns number corresponding to the hour (24-hour clock format) in the given time.

Hour( DateTime )

Number

Hour (Newdate( 2021,05,19,11,30,'AM')) = 11

Minute

Returns the number corresponding to the minute in the given time.

Minute( DateTime )

Number

Minute (Newdate( 2021,05,19,11,30,'AM')) = 30

Month

Returns the number corresponding to the month in the given date.

Month( DateTime )

Number

Month (Newdate( 2021,05,19,11,30,'AM')) = 5

Year

Returns the number corresponding to the year in the given date.

Year( DateTime )

Number

Year (Newdate( 2021,05,19,11,30,'AM')) = 2021

Weekday


Returns the number corresponding to the weekday in the given date.
 
Note: 1- Sunday, 2 - Monday,    3 - Tuesday and so on

Weekday( DateTime )

Number

Weekday (Newdate( 2021,05,19,11,30,'AM')) = 3

 

Fromnow

Returns the difference between current date and input date.

Fromnow( DateTime )

Number

Fromnow(Newdate ( 2021,10,26,11,00,'AM')) = 3d 1h (If current date is 23-10-2021 10:00 AM)

Now

Returns current date and time.

Now()

DateTime, Date

Now() = 25/01/2022 7:28 PM (if current date and time is 25 Jan 2022 7:28 PM)

Todaystart

Returns Today's date with business start time.

Todaystart(setbusinessstart[boolean]**)

 

DateTime, Date

Todaystart(false) =25/01/2022  12:00 AM (if current date is 25 Jan 2022)

Todaystart(true) = 25/01/2022 9:00 AM   (if business hour starts at 9:00 AM)

 

Note:
true - return today's business hour start time
false - return today's start time

Todayend

Returns Today's date date with business end time.

Todayend(setbusinessend[boolean]**)

 

DateTime, Date

Todayend(false) = 25/01/2022 11:59 PM   (if current date is 25 Jan 2022)

Todayend(true) = 25/01/2022 6:00 PM (if business hour ends at 6:00 PM)

 

Note:
true - return today's business hour end time
false - return today's end time

Formatdate

Formats the date in the given format.

Formatdate(DateTime, dateTimeFormat[String]**)

 

Note: dateTimeFormat is an optional parameter - If not given date and time will be displayed based on org date and time settings

String

Formatdate (Newdate( 2021,12 ,26 ,06 ,30 ,'AM' ), 'dd/MM/yyyy hh:mm aa') = '26/12/2021 06:30 AM'

Formatduration

Formats the duration given in milliseconds as a string.

Formatduration( durationInMilliSeconds, separator[String]** )

Note: separator='  ' is the defaultValue.

String

Formatduration( 4202349 ) =   1h 10m 2s

Durationtodays

Convert duration given in milliseconds to days.

Durationtodays( durationinMilliseconds[Number] )

Number

Durationtodays( 2678400000) = 31

Durationtohours

Convert duration given in milliseconds to hours.

Durationtohours( durationinMilliseconds[Number] )

Number

Durationtohours( 18000000 ) = 5

Durationtominutes

Convert duration given in milliseconds to minutes.

Durationtominutes( durationinMilliseconds[Number] )

Number

Durationtominutes( 18000000 ) = 300

     
 
To create a DateTime formula field,  
  1. Drag and drop the Formula custom field.
  2. Enter the name of your field.
  3. Choose a return type.
  4. Choose Date Time in the All Functions drop-down.
  5. You can either enter your own formula expression or create your expression using the provided fields.
  6. Choose the privacy preferences and click Create .

Boolean Formula Field  

A boolean function calculates the result based on the logical manipulation of the input. It determines the entities with just two values, true or false, along with and, or, and not.
 
The following table will help you understand the types of arguments required by boolean functions and the type of syntax to form.  
 
 Function Name Description Syntax Return Type Example
If
Returns one of the two values, depending on a logical condition.
If(boolean, generic1, generic2)
Generic
If(5>7,1,0) = 0
If(8>7,'true','false') = 'true'
And
Returns 'true' if all expressions are true. Otherwise returns 'false'.
And(boolean1,boolean2, ...)
Boolean
And(5>6,4==4,6>7) = false
And(5<6,4==4,8>7) = True
Or
Returns 'true' if any one of the expressions is true. Otherwise returns 'false'.
Or(boolean1,boolean2, ...)
Boolean
Or(5<6,4==4,6>7) = True
Or(2>6,4==3,3>7) = false
Not
Returns the logical negative of the given expression.
Not(boolean)
Boolean
Not(true) = false
Not(false) = true
 
To create a boolean formula field,
  1. Drag and drop the Formula custom field
  2. Enter the name of your field.
  3. Choose a return type.
  4. Choose Boolean in the All Functions drop-down.
  5. You can either enter your own formula expression or create your expression using the provided fields.
  6. Choose the privacy preferences and click Create.
Notes
Note:  
  1. Return Type of a formula field cannot be edited after creating it.
  2. A maximum of 10 fields can be used in a formula field.
  3. A maximum of 5 nested functions can be used in a field.
  4. Formula fields cannot be set mandatory.
  5. If the empty field is numeric, it will be considered as 0, if it is string it’ll be considered as "".