Zoho Sheet - Zoho CRM Formula Fields Reference Chart

Numeric

FUNCTION	DESCRIPTION	ARGUMENTS	ARGUMENT DATA TYPE	RETURN TYPE	USAGE	EXAMPLE
Numeric Functions
Abs	Returns the absolute value of the Number.	1	Number	Number	Abs(number)	Abs(-42) returns 42 Abs(+33) returns 33
Ceil	Returns the smallest integer greater to or equal to the input number.	1	Number	Number	Ceil(number)	Ceil(3.4) returns 4 Ceil(-3.4) returns -3
Floor	Returns the largest integer less than or greater to the input number.	1	Number	Number	Floor(number)	Floor(3.8) returns 3 Floor(-3.4) returns -4
Naturallog	Returns the natural logarithm of a number.	1	Number	Number	Naturallog(number)	Naturallog(1) returns 0.69 Naturallog(10) returns 2.40
Base10log	Returns the base 10 logarithm of the input number.	1	Number	Number	Base10log(number)	Base10log(10) returns 1.0 Base10log(1) returns 0
Max	Returns the maximum value from the specified list of compatible data types.	Multiple	All Number	Number	Max(value1,value2,...)	Max(3,1,5,2) returns 5 Max(0,-4,-3,2) returns 2
Min	Returns the minimum value from the specified list of compatible data types.	Multiple	All Number		Min(value1,value2,...)	Min(5,-3,0,1) returns -3 Min(5,0,.01,0.5) returns 0.0
Sqrt	Returns the square root of the input number.	1	Number	Number	Sqrt(number)	Sqrt(4) returns 2 Sqrt(9) returns 3

String

FUNCTION	DESCRIPTION	ARGUMENTS	ARGUMENT DATA TYPE	RETURN TYPE	USAGE	EXAMPLE
String Functions
Len	Returns the number of characters in a specified text string.	1	String	Number	Len(string)	Len('abc') returns 3 Len(' abc ') returns 5
Find	Returns the nth occurrence of the text string.	3	String,String,Number	Number	Find('string','search string', number)	Find('greenery','n',1) returns 5
Concat	Returns the concatenation of all the strings.	Multiple	All string	String	Concat('string','string',...)	Concat('FirstName',' ','LastName') returns FirstName LastName
Contains	Returns true if search string is found in the other string, otherwise returns false.	2	String,String	Boolean	Contains('string', 'search string')	Contains('abcdef','cd') returns true
Startswith	Returns true if the string begins with the search string, otherwise returns false.	2	String,String	Boolean	Startswith('string','search string')	Startswith('abcdef','cd') returns false Startswith('abcdef','abc') returns true
Endswith	Returns true if the string ends with the search string, otherwise returns false.	2	String,String	Boolean	Endswith('string', 'search string')	Endswith('abcdef','ab') returns false Endswith('abcdef','ef') returns true
Lower	Converts all characters in a string to lower case.	1	String	String	Lower('string')	Lower('APPLES') returns "apples" Lower('Apples') returns "apples"
Upper	Converts all characters in a string to upper case.	1	String	String	Upper('string')	Upper('apples') returns "APPLES" Upper('APPles') returns "APPLES"
Trim	Returns string with the leading and trailing white space characters removed.	1	String	String	Trim('string')	Trim(' abcd ') returns "abcd"
Substring	Returns a portion of an input string, from a start position in the string to the specified length.	3	String,Number,Number	String	Substring('string',n1,n2)	Substring('abcdefg',4,7) returns "defg"
Replace	Replaces each occurrence of the search string in the input string with the corresponding replace string.	3	String,String,String	String	Replace('string','search string','replace string')	Replace('abcdefg','abc','xyz') returns "xyzdefg"
Tostring	Converts any argument to a string data type.	1	Generic(any data type)	String	Tostring(generic argument)	Tostring(3.4) returns "3.4" Tostring('') returns <empty>

DateTime

FUNCTION	DESCRIPTION	ARGUMENTS	ARGUMENT DATA TYPE	RETURN TYPE	USAGE	EXAMPLE
DateTime Functions
Newdate	Creates a date from the year, month, day and time..	6	Number,Number,Number, Number,Number,String	DateTime	Newdate(year,month,day, hour,minute,'string')	Newdate(2007,12,21,06,30,'AM') returns "21/12/2007 06:30 AM" *
Datepart	Returns the date value for the date time expression.	1	DateTime	String	Datepart(datetime argument)	Datepart(Newdate(2007,12,21,06,30,'AM')) returns "21/12/2007" *
Timepart	Returns the time value for the date time expression.	1	DateTime	String	Timepart(datetime argument)	Timepart(Newdate(2007,12,21,06,30,'AM')) returns "06.30 AM"
Adddate	Returns the date obtained by adding n (year/day/month/hour/min) to the given date.	3	DateTime,Number,String	DateTime	Adddate(datetime,number, 'string')	Adddate(Newdate(2007,12,21,06,30,'AM'),2,'YEAR') returns "21/12/2009 06:30 AM" **
Subdate	Returns the date obtained by subtracting n (year/day/month/hour/min) to the given date.	3	DateTime,Number,String	DateTime	Subdate(datetime,number, 'string')	Subdate(Newdate(2007,12,21,06,30,'AM'),2,'YEAR') returns "21/12/2005 06:30 AM" **
Now	Returns a date/time representing the current moment.	0	-	DateTime	Now()	Now() returns "19/05/2009 10:52 AM"
Datecomp	Compares two dates and returns the difference of days in minutes.	2	DateTime,DateTime	Number	Datecomp(Datetime, Datetime)	Datecomp(Newdate(2009,05,19,11,30,'AM'), Newdate(2009,05,19,12,30,'AM')) returns 660.0 ***
Dayofmonth	Returns the day of the month for the given date.	1	DateTime	Number	Dayofmonth(Datetime)	Dayofmonth(Newdate(2009,05,19,11,30,'AM')) returns "19.0"
Hour	Returns the hour corresponding to the given date.	1	DateTime	Number	Hour(Datetime)	Hour(Newdate(2009,05,19,11,30,'AM')) returns "11.0"
Minute	Returns the minute corresponding to the given date.	1	DateTime	Number	Minute(Datetime)	Minute(Newdate(2009,05,19,11,30,'AM')) returns "30.0"
Month	Returns the month corresponding to the given date.	1	DateTime	Number	Month(Datetime)	Month(Newdate(2009,05,19,11,30,'AM')) returns "5.0"
Year	Returns the year corresponding to the given date.	1	DateTime	Number	Year(Datetime)	Year(Newdate(2009,05,19,11,30,'AM')) returns "2009.0"
Weekday	Returns the weekday corresponding to the given date.	1	DateTime	Number	Weekday(Datetime)	Weekday(Newdate(2009,05,19,11,30,'AM')) returns "3.0". (19th May is Tuesday)





* The input DateTime format should always be YYYY/MM/DD HH:MM but the output will be displayed as per the selected Country Locale. The string data type (YEAR/DAY/MONTH/HOUR/MIN) should be in UPPERCASE. * The resulting return value for the Datecomp function is always displayed in minutes.

Boolean

FUNCTION	DESCRIPTION	ARGUMENTS	ARGUMENT DATA TYPE	RETURN TYPE	USAGE	EXAMPLE
Numeric Functions
If	Returns one of two values, depending on the value of a given logical condition. If the boolean test is true, If() returns the true value, otherwise returns the false value.	3	Boolean,Generic,Generic	Generic	If(Boolean,Generic, Generic)*	If(8>7,1,0) returns "1.0" If(8>7,'True','False') returns "True"
And	Returns a true response if all expressions are true; returns a false value even if one of the expressions is false.	Multiple	All Boolean	Boolean	And(boolean,boolean ...)	And(2>1,5>3,7<8) returns "True" And(2>1,5>3,7>8) returns "False"
Or	Returns true if any one expression is true. Returns false if all expressions are false.	Multiple	All Boolean	Boolean	Or(boolean,...)	Or(2>1,3>5,7>8) returns "True" Or(1>2,3>5,7>8) returns "False"
Not	Returns the logical negation of the given expression (If the expression is true, returns false).	1	Boolean	Boolean	Not(boolean)	Not(false) returns "True" Not(true) returns "False"




* Generic implies any data type - Number, String, Datetime (including normal date) or Boolean. The return type depends on the selected data type. For instance, if the generic data type is a number, the return type should be Numeric and not string or boolean.