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,

Number
String
Date
DateTime
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,

Drag and drop the Formula custom field
Enter the name of your field.
Choose a return type.
Choose Numeric in the All Functions drop-down.
You can either enter your own formula expression or create your expression using the provided fields.
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,

Drag and drop the Formula custom field
Enter the name of your field.
Choose a return type.
Choose String in the All Functions drop-down.
You can either enter your own formula expression or create your expression using the provided fields.
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,

Drag and drop the Formula custom field.
Enter the name of your field.
Choose a return type.
Choose Date Time in the All Functions drop-down.
You can either enter your own formula expression or create your expression using the provided fields.
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,

Drag and drop the Formula custom field
Enter the name of your field.
Choose a return type.
Choose Boolean in the All Functions drop-down.
You can either enter your own formula expression or create your expression using the provided fields.
Choose the privacy preferences and click Create.

Note:

Return Type of a formula field cannot be edited after creating it.
A maximum of 10 fields can be used in a formula field.
A maximum of 5 nested functions can be used in a field.
Formula fields cannot be set mandatory.
If the empty field is numeric, it will be considered as 0, if it is string it’ll be considered as "".

Access your files securely from anywhere

Zoho CRM Training Programs

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

Redefine the way you work

with Zoho Workplace

Start your free trial

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.

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

Zoho Workerly Home
Forums
Connect With Us:

Zoho Recruit Home
Forums
Connect With Us:

Start tracking your website metrics today

Use PageSense to understand what your visitors are looking for, how they are behaving, and where they are facing problems on your website.

Ready to improve your website's performance?

Install the PageSense code snippet on your site in a matter of minutes and start collecting in-depth data about the website visitors to grow your business.

Track and measure every business goal

Set up goals in PageSense to measure every single action performed by visitors on your website like button or link clicks, form submissions, and page engagements.

Understand the customer journey on your website

Create funnels in PageSense to quickly see which pages visitors use to enter your website, where they navigate to next, and which pages they decide to leave without converting.

Visualize your visitor's behavior with color codes

Set up heatmaps in PageSense to see where users have clicked more, how far they've scrolled, and on which parts of a page they've spent the most time using color-coded patterns in reports.

Measure customer engagement with your web forms

Use form analytics in PageSense to see how people interact with different fields in your form, whether they complete the form successfully or not, and where exactly they drop out on your form.

Record real visitor interactions on your website

Use session recordings in PageSense to watch a video of all the visitor actions performed on your website including the pages they navigate, the buttons they click, the UX issues they face, and more.

Test and optimize webpages for better conversions

Run A/B or Split URL tests in PageSense to figure out which version of your web page works best for your business and results in the best conversion rate.

Give each customer a unique website experience

Use personalization in PageSense to deliver customized versions of your website for every individual customer based on their demographics, local weather, browsing history, and more.

Grow your business through customer feedback

Run polls on your website using PageSense to understand what your customers think about your products/services and what needs improvement on your site.

Send timely updates that customers love

Use web push notifications in PageSense to schedule and notify your customers about an upcoming flash sale, product releases, promotional coupons, and a lot more that can spark conversions on your website.

Advertise your business to website visitors

Use pop-ups in PageSense to instantly grab the attention of visitors by showing attractive signup offers, coupon code discounts, or email newsletters that can eventually convert them into subscribers.

Access more advanced settings in PageSense

Use PageSense's advanced features like creating mutually exclusive groups, enabling cross-domain tracking, configuring customized project JS, and more to get deeper insights about your website.

Try easy-to-use extensions

Download the PageSense extension app available for your web browser with a few clicks and start collecting all of your required website metrics in real time.

Discover your favorite integrations with PageSense

Get a deeper look at your website's data by seamlessly integrating PageSense with a host of popular third-party apps like Google Analytics, Mixpanel, Intercom, and more.

Quick Links	Workflow Automation	Data Collection
Web Forms	Enterprise	Begin Data Collection
Interactive Forms	Workplace	Data Collection App
Offline Forms	Customer Service	Accessible Forms
Digital Forms	Marketing	Forms for Small Business
HTML Forms	Education	Forms for Enterprise
Contact Forms	E-commerce	Forms for any business
Lead Generation Forms	Healthcare	Forms for Startups
Wordpress Forms	Customer onboarding	Order Forms for Small Business
No Code Forms	Construction	RSVP tool for holidays
Free Forms	Travel
Prefill Forms	Non-Profit
Intake Forms	Legal
Form Designer	HR
Card Forms	Food
Assign Forms	Photography
Translate Forms	Real Estate
Electronic Forms
Notification Emails for Forms	Alternatives
Holiday Forms	Google Forms alternative
Form to PDF	Jotform alternative
Email Forms CRM Forms Embeddable Forms Drag and Drop form builder HIPAA Forms Encrypted Forms

New to Zoho CRM Plus?

Deliver unforgettable customer experiences

ACCESS ZOHO CRM PLUS

New to Zoho CRM Plus?

Deliver unforgettable customer experiences

ACCESS ZOHO CRM PLUS

New to Zoho Marketing Plus?

Everything you need to run your marketing

ACCESS ZOHO MARKETING PLUS

New to Zoho Marketing Plus?

Everything you need to run your marketing

ACCESS ZOHO MARKETING PLUS

Zoho Pagesense Resources

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.

New to Zoho Survey?

Access Zoho Survey

Latest Feature Updates

Explore our Pricing & Plans

Zoho Survey Resources

Android Mobile Surveys

Insurvey Blogs

Insight Blogs

Tips & Tricks

New to Zoho Social?

Manage your brands on social media

Access Zoho Social

Zoho Desk Resources

Desk Community Learning Series
Digest
Functions
Meetups
Kbase
Resources
Glossary
Desk Marketplace
MVP Corner
Word of the Day

Zoho Sheet Resources

New to Zoho Forms?

Latest Feature Updates

Explore our Pricing & Plans

Zoho Forms Resources

Secure your business

communication with Zoho Mail

Get started

Mail on the move with

Zoho Mail mobile application

Go Mobile

Stay on top of your schedule

at all times

Get started

Carry your calendar with you

Anytime, anywhere

Get started

Latest Announcements

New to Zoho Sign?

Zoho Sign Resources

Sign, Paperless!

Sign and send business documents on the go!

Get Started Now

Zoho SalesIQ Resources

New to Zoho TeamInbox?

Zoho TeamInbox Resources

Connect with us

New to Zoho ZeptoMail?

LEARN MORE

Latest Feature Updates

Zoho DataPrep Resources

New to Zoho DataPrep?

Access Zoho DataPrep

Zoho DataPrep Demo

Get a personalized demo or POC

Design. Discuss. Deliver.

Create visually engaging stories with Zoho Show.

Get Started Now

New to Zoho Workerly?

Access Zoho Workerly

New to Zoho Recruit?

Access Zoho Recruit

New to Zoho CRM?

Signup Now

Access Zoho CRM

Latest Feature Updates

New to Zoho Projects?

Access Zoho Projects

New to Zoho Sprints?

Access Zoho Sprints

New to Zoho Assist?

Access Zoho Assist

New to Bigin?

Signup Now

Access Bigin

Latest Feature Updates

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