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 |
| | | | |
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' |
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]** ) | 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 |
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 |
Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.
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.
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.