Formula Custom Fields

Formula Custom Fields

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

    Zoho CRM Training Programs

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

    Zoho CRM Training
      Redefine the way you work
      with Zoho Workplace

        Zoho DataPrep Personalized Demo

        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.

        Zoho CRM Training

          Create, share, and deliver

          beautiful slides from anywhere.

          Get Started Now


            Get started with Zoho Sign

            in a few quick steps!

            Download Help Guide





                      Still can't find what you're looking for?

                      Write to us:  support@zohoforms.com


                            




                          

                        Zoho Marketing Automation

                          Zoho Sheet Resources

                           




                              Zoho Forms Resources


                                Secure your business
                                communication with Zoho Mail


                                Mail on the move with
                                Zoho Mail mobile application

                                  Stay on top of your schedule
                                  at all times


                                  Carry your calendar with you
                                  Anytime, anywhere




                                        Zoho Sign Resources

                                          Sign, Paperless!

                                          Sign and send business documents on the go!

                                          Get Started Now


                                              Zoho SalesIQ Resources



                                                  Zoho TeamInbox Resources



                                                          Zoho DataPrep Resources



                                                            Zoho DataPrep Demo

                                                            Get a personalized demo or POC

                                                            REGISTER NOW


                                                              Design. Discuss. Deliver.

                                                              Create visually engaging stories with Zoho Show.

                                                              Get Started Now











                                                                                    • Related Articles

                                                                                    • Create and manage fields

                                                                                      Custom fields are the important aspect in shaping a layout. A layout is structured with a list of fields that are specific to your requirements. Once the user creates a layout they can start adding custom fields.    Default Fields: The default fields ...
                                                                                    • Sprint Custom Fields

                                                                                      You can customize the layouts for your Sprints and associate additional fields with them.  Go to  Setup  on the top right corner and click  Sprints  under  Custom Layouts and Fields . You will find the  Standard Layou t which will be applied to your ...
                                                                                    • Custom View

                                                                                      Customize the work items based on specific criteria and save these work items across different views.  You can access custom views only if you are on the Premier and Professional plans.  All the users in the team can access custom views irrespective ...
                                                                                    • General

                                                                                      What are custom layouts? Every project will have a different set of goals. The workforce, requirements, efficiency of employees, type of work etc. varies based on the project's goal. Having a standard layout for all the projects is difficult to ...
                                                                                    • Custom Layouts for Projects

                                                                                      You can customize the layouts for your projects and associate additional fields with them. We have added a new default field called 'Project Stages ' that lets you include the status of the project.  Project Stage  is a pickup custom field that has ...
                                                                                    Wherever you are is as good as
                                                                                    your workplace

                                                                                      Resources

                                                                                      Videos

                                                                                      Watch comprehensive videos on features and other important topics that will help you master Zoho CRM.



                                                                                      eBooks

                                                                                      Download free eBooks and access a range of topics to get deeper insight on successfully using Zoho CRM.



                                                                                      Webinars

                                                                                      Sign up for our webinars and learn the Zoho CRM basics, from customization to sales force automation and more.



                                                                                      CRM Tips

                                                                                      Make the most of Zoho CRM with these useful tips.



                                                                                        Zoho Show Resources