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


            Zoho Sign now offers specialized one-on-one training for both administrators and developers.

            BOOK A SESSION










                                    You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.




                                        Manage your brands on social media

                                          Zoho Desk Resources

                                          • Desk Community Learning Series


                                          • Digest


                                          • Functions


                                          • Meetups


                                          • Kbase


                                          • Resources


                                          • Glossary


                                          • Desk Marketplace


                                          • MVP Corner


                                          • Word of the Day


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

                                                                                                      • Zoho Sprints HIPAA Compliance Guide

                                                                                                        The Health Insurance Portability and Accountability Act (including the Privacy Rule, Security Rule, Breach notification Rule, and Health Information Technology for Economic and Clinical Health Act) ("HIPAA"), requires Covered Entities and Business ...
                                                                                                      • Zoho Sprints GDPR Compliance

                                                                                                        Data privacy and security are the core elements that every business should guarantee its customers with. Keeping this in mind, the European Union's General Data Protection Regulation (GDPR) came into discussion. It enforces all the businesses across ...
                                                                                                      • Create and manage custom 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. You can create custom fields for ...
                                                                                                      • Export sprints

                                                                                                        You can export the sprint data in an xls or csv file and download it to use outside of the application. This can be used to transform data from Zoho Sprints into a file that you can process in third party apps or other in-house devices. Benefits ...
                                                                                                      • Custom domain

                                                                                                        A domain is the text that you key into the address bar when you're trying to locate a source on the web. If you want to locate Zoho's website you will enter https://www.zoho.com on the browser. Like all other Zoho applications, Zoho Sprints is hosted ...
                                                                                                        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