Functions available to add formula columns

Abs

abs(num)

Returns the absolute value of the numeric value.

Parameters

Name	Description
num Decimal	Parameter must be a numeric value from a column or an expression that returns a number.

Name

Description

num

Decimal

Parameter must be a numeric value from a column or an expression that returns a number.

Example

Function	Result
abs(-1)	1

Div

div(numerator,denominator)

Performs floating-point division. Returns a decimal value.

Parameters

Name	Description
numerator Decimal	Parameter must be a number, a numeric column or an expression that returns a number.
denominator Decimal	Parameter must be a number, a numeric column or an expression that returns a number.

Name

Description

numerator

Decimal

Parameter must be a number, a numeric column or an expression that returns a number.

denominator

Decimal

Parameter must be a number, a numeric column or an expression that returns a number.

Example

Function	Result
div(3,2)	1.5

Add

add(num1,num2)

Returns param1+param2. Performs numeric addition.

Parameters

Name	Description
num1 Decimal	Parameter must be a numeric value or a Number column.
num2 Decimal	Parameter must be a numeric value or a Number column.

Name

Description

num1

Decimal

Parameter must be a numeric value or a Number column.

num2

Decimal

Parameter must be a numeric value or a Number column.

Example

Function	Result
add('3', '2')	5

Sub

sub(param1,param2)

Performs numeric subtraction. Returns param1 - param2.

Parameters

Name	Description
param1 Decimal	Parameter must be a numeric value.
param2 Decimal	Parameter must be a numeric value.

Name

Description

param1

Decimal

Parameter must be a numeric value.

param2

Decimal

Parameter must be a numeric value.

Example

Function	Result
sub('3', '2')	1

Mul

mul(param1,param2)

Performs numeric multiplication.

Parameters

Name	Description
param1 Decimal	Parameter must be a numeric value.
param2 Decimal	Parameter must be a numeric value.

Name

Description

param1

Decimal

Parameter must be a numeric value.

param2

Decimal

Parameter must be a numeric value.

Example

Function	Result
mul('3', '2')	6

Avg

avg(list)

Returns the average value in a numeric column or list

Parameters

Name	Description
list Decimal	Parameter must be a numeric column, or a list of numbers

Name

Description

list

Decimal

Parameter must be a numeric column, or a list of numbers

Example

ticket_count
30
40
50
60
70
80
90

Function	Result
avg(ticket_count);	60

Min

min(num)

Returns the minimum value in a list of numeric values

Parameters

Name	Description
num Decimal	Parameter must be a numeric column or a list of numeric values

Name

Description

num

Decimal

Parameter must be a numeric column or a list of numeric values

Example

ticket_count
30
40
50
60
70
80
90

Function	Result
min('ticket_count');	30

Max

max(num)

Returns the maximum value from a numeric column

Parameters

Name	Description
num Decimal	Parameter must be a numeric column

Name

Description

num

Decimal

Parameter must be a numeric column

Example

ticket_count
30
40
50
60
70
80
90

Function	Result
max('ticket_count');	90

Max Date

max_date(date)

Returns the maximum date value from a date or a date-time column.

Parameters

Name	Description
date Datetime	Parameter must be a date column or a date-time column.

Name

Description

date

Datetime

Parameter must be a date column or a date-time column.

Example

Data column
06 Dec 2018
17 Nov 2019
20 Oct 2020

Function	Result
max_date(Data column)	20 Oct 2020

Min Date

min_date(date)

Returns the minimum date value from a date or a date-time column.

Parameters

Name	Description
date Datetime	Parameter must be a date column or a date-time column.

Name

Description

date

Datetime

Parameter must be a date column or a date-time column.

Example

Data column
06 Dec 2018
17 Nov 2019
20 Oct 2020

Function	Result
min_date(Data column)	06 Dec 2018

Sum

sum(num)

Returns the sum of the values in the given column

Parameters

Name	Description
num Decimal	Parameter must be a numeric column, or a list of numbers

Name

Description

num

Decimal

Parameter must be a numeric column, or a list of numbers

Example

marks
45
33
56
70

Function	Result
sum(marks)	205

Mode

mode(num)

Returns the most occurring value in the column

Parameters

Name	Description
num Decimal	Parameter must be a numeric column

Name

Description

num

Decimal

Parameter must be a numeric column

Example

ticket_count
30
40
30
60
30
80
90

Function	Result
mode('ticket_count');	30

Count

count(col)

Returns the total number of values in the column. Null values (empty cells) are ignored.

Parameters

Name	Description
col Text	Parameter must be a column

Name

Description

col

Text

Parameter must be a column

Example

ticket_count
30
40
50
60
70
80
90

Function	Result
count(ticket_count);	7

Count with Blanks

count_wb(col)

Returns the total number of values in the column including null values (empty cells).

Parameters

Name	Description
col Text	Parameter must be a column

Name

Description

col

Text

Parameter must be a column

Example

ticket_count
30
40
50

70
80
90

Function	Result
count_wb(ticket_count);	7

Variance

variance(param)

Returns the variance calculated from values of a group in a number format.

Parameters

Name	Description
param Decimal	Parameter must be a numeric group of values such as a Number column.

Name

Description

param

Decimal

Parameter must be a numeric group of values such as a Number column.

Example

ticket_count
30
40
50
60
70
80
90

Function	Result
variance("ticket_count")	466.66

Standard Deviation

stddev(param)

Returns the standard deviation calculated from the input values.

Parameters

Name	Description
param Decimal	Parameter must be a numeric value or a Number column.

Name

Description

param

Decimal

Parameter must be a numeric value or a Number column.

Example

Score
10
20
30
40
50
60

Function	Result
stddev(Score)	18.70

Binary

bin(param)

Returns the binary representation of a number.

Parameters

Name	Description
param Number	Parameter must be a numeric value or a numeric column or an expression that returns a number.

Name

Description

param

Number

Parameter must be a numeric value or a numeric column or an expression that returns a number.

Example

Function	Result
bin('111')	1101111

Cube Root

cbrt(param)

Returns the cube root of the number.

Parameters

Name	Description
param Decimal	Parameter must be a number, or a numeric column or an expression that results in a numeric value.

Name

Description

param

Decimal

Parameter must be a number, or a numeric column or an expression that results in a numeric value.

Example

Function	Result
cbrt('27')	3

Ceiling

ceil(param)

Returns the smallest integer value that is bigger than or equal to a number.

Parameters

Name	Description
param Decimal	Parameter must be a number, or a numeric column or an expression that results a numeric value.

Name

Description

param

Decimal

Parameter must be a number, or a numeric column or an expression that results a numeric value.

Example

Function	Result
ceil('25.75')	26

Character

char(param,...)

Returns the ASCII character having the binary equivalent to the parameter. If n is larger than 256 the result is equivalent to char(n % 256).

Parameters

Name	Description
param Multiple	Parameter must be a number, a numeric column, or an expression that returns a number.

Name

Description

param

Multiple

Parameter must be a number, a numeric column, or an expression that returns a number.

Example

Function	Result
char('65')	A

Concat

concat(params)

Returns the concatenated text of the given parameters. Returns null, if any one of the parameters is null.

Parameters

Name

Description

params

Text

Accepts any number of parameters. Parameters must be a Text or a Text column, or an expression that returns a Text.

Example

Function	Result
concat("NOW", "HERE")	NOWHERE

Concat with Separator

concat_ws(params)

Returns the concatenated text of the given parameters along with the separator. Returns null, if any one of the parameter is null.

Parameters

Name

Description

params

Text

The first of the params is considered as the separator, using which the rest of the parameters will be concatenated. All parameters must be Text, Text columns, or expressions that returns a Text.

Example

Function	Result
concat_ws('_', 'NOW', 'HERE')	NOW_HERE

Convert

conv(param,from_base,to_base)

This function converts the number from 'from base' to 'to base'.

Parameters

Name

Description

param

Text

Parameter must be a number or a number column, or an expression that returns a number.

from_base

Decimal

The base value to be converted from.

to_base

Decimal

The base value to be converted to.

Example

Function	Result
conv(100, 2, 10)	4

Current Date

current_date()

Returns the current date as a date value. Function accepts no parameters.

Example

Function	Result
current_date()	09/21/2019

Current Timestamp

current_timestamp()

Returns the current timestamp as a date-time value. Function accepts no parameters.

Example

Function	Result
current_timestamp()	09/19/2019 19:18:55

Date Format

date_format(date,format)

Converts the date-timestamp to the format specified in the parameter.

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column to change the format.

format

Text

Specifies the format in which the date should be returned by the function.

Example

Data column
2017-06-15

Function	Result
date_format(Data column, "dd MMM, yyyy")	15 June, 2017

Note: If you wish to provide the date input directly into the function as shown below, please use either of these supported formats: “dd MMM, yyyy” or “dd MMM, yyyy HH:mm:ss"

Example function:

date_format(14 June 2017 18:20:56, dd MMM, yyyy HH:mm:ss)

Function output:

2017-06-14 18:20:56

Day of Month

day_of_month(date)

Returns the day of month of the date.

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column.

Example

Data column
2009-03-24

Function	Result
day_of_month(Data column)	24

Day of Week

day_of_week(date)

Returns the day of the week for the given date. [1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday]

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column.

Example

Data column
06-12-2018

Function	Result
day_of_week(Data column)	5

Week Day

weekday(date)

Returns the day of the week from the given date

Parameters

Name

Description

date

Datetime

Parameter must be a date column or a date-time column

Example

Data column
06 Dec 2018

Function	Result
weekday(Data column);	3

Day of the Week in name

day_of_week_name(date)

Returns the day of the week (as name) for the given date.

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column.

Example

Data column
06 Dec 2018

Function	Result
day_of_week_name(Data column)	Thursday

Day of the Year

day_of_year(date)

Returns the day of year (in number) of the date/timestamp.

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column.

Example

Data column
25-03-2009

Function	Result
day_of_year(Data column)	84

Decode

decode(param,charset)

Returns the decoded value of the first argument using the second argument character set (UTF-8, ASCII, UTF-10, etc.).

Parameters

Name

Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.

charset

Text

Specifies the character set such as UTF-8, ASCII, UTF-10, etc.

Example

Function	Result
decode("ABC", "UTF-8")	ABC

Degrees

degrees(param)

Converts radians to degrees.

Parameters

Name

Description

param

Decimal

Specifies the angle in radians.

Example

Function	Result
degrees(3.141592653589793)	180.0

Encode

encode(param,charset)

Returns the encoded value of the first argument using the second argument character set (utf-8, ascii, utf-10, etc.).

Parameters

Name

Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.

charset

Text

Specifies the character set. 'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16' are the supported character sets.

Example

Function	Result
encode("ABC", "UTF-8")	ABC

Exponential

exp(param)

Returns the exponential value of the given number. This value is the exponent (e) raised to the power specified as the parameter.

Parameters

Name

Description

param

Decimal

Parameter must be a number or an expression that returns a number.

Example

Function	Result
exp(1)	2.718281828

Floor

floor(param)

Returns the largest integer that is less than or equal to the given parameter.

Parameters

Name

Description

param

Decimal

Parameter must be a number or an expression that returns a number.

Example

Function	Result
floor(-0.1)	-1

Greatest

greatest(params)

Returns the largest value from a group of values.

Parameters

Name

Description

params

Decimal

Supports multiple parameters, The parameters must be a number column, or a list of numbers or an expression that returns a list of numbers.

Example

Function	Result
greatest(10, 9, 2, 4, 3)	10

Hexadecimal

hex(param)

Returns the hexadecimal value of the given parameter.

Parameters

Name

Description

param

Text

Parameter can be any value or a column of Number, Text or Boolean type.

Example

Function	Result
hex(17)	11

Hour

hour(datetime)

Returns the hour component of the timestamp.

Parameters

Name

Description

datetime

Datetime

Specifies a Time or a Datetime column.

Example

Data column
2009-03-07 20:18:34:543

Function	Result
hour(Data column)	20

If

if(condition,param1,param2)

If the condition is true, then returns param1; otherwise returns param2.

Parameters

Name

Description

condition

Boolean

An expression that returns a Boolean.

param1

Text

Parameter can be any value or a column of any type.

param2

Text

Parameter can be any value or a column of any type.

Example

Function	Result
if(1 < 2, 'a', 'b')	a

You can also use nested if and else if conditions using the If function. Here are some examples.

Nested If

if(condition1,if(condition2,param1,param2),param3)

If condition1 is true, condition2 is validated. When condition2 is true, returns param1; otherwise returns param2. If condition1 is false, then returns param3.

Example

Function	Result
if(1<3, if(1<2, 'a', 'b'), 'c')	a

Else if

if(condition1, param1, if(condition2,param2,param3))

If condition1 is true, returns param1. When condition1 is false, condition2 is validated. When condition2 is true, returns param2; otherwise returns param3.

Example

Function	Result
if(3<2, 'a', if(3<4, 'b', 'c'))	b

If Null

if_null(param1,param2)

Returns param2 if param1 is null, returns param1 otherwise.

Parameters

Name

Description

param1

Text

Parameter can be any value or a column of any type.

param2

Text

Parameter can be any value or a column of any type.

Example

Function	Result
if_null(null, 'b')	b

Initcap

initcap(param)

Returns the text with the first letter of each word in uppercase. All other letters are in lowercase. Words are delimited by white space.

Parameters

Name

Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

Example

Function	Result
initcap('zoho one is an operating system for your business')	Zoho One Is An Operating System For Your Business

Is Null

is_null(param)

Returns true if the expression or column value is null or empty

Parameters

Name

Description

param

Text

Parameter can be a column, or an expression

Example

Function	Result
is_null(null)	true
is_null('A')	false

Is not Null

is_not_null(param)

Returns true if the column value or expression is not null or empty

Parameters

Name

Description

param

Text

Parameter can be a column, or an expression

Example

Function	Result
is_not_null(null)	false
is_not_null('A')	true

Not

not(param)

Applies the NOT logical operation on the column. Returns the opposite value of the Boolean value specified.

Parameters

Name

Description

param

Boolean

Specifies a Boolean value on which the not logical operation is to be applied on.

Example

Function	Result
not(5 > 3)	false

Last Day

last_day(datetime)

Returns the last day of the month mentioned in the given date.

Parameters

Name

Description

datetime

Datetime

Specifies a date column or a datetime column.

Example

Data column
2016-04-09

Function	Result
last_day(Data column)	2016-04-30

Least

least(params)

Returns the least value of all, skipping the null values.

Parameters

Name

Description

params

Decimal

The parameters must be a number column, or a list of numbers or an expression that returns a list of numbers.

Example

Age
[20, 21, 24, 45, 46, 54, 60, 61, 63]

Weight
[123, 145, 160, 155, 200, 50, 134, 55, 60]

Function	Result
least('Age', 'Weight')	20, 21, 24, 45, 46, 50, 60, 55, 60

Left

left(param,length)

Returns the leftmost 'length' of characters from the first parameter, if the length is less than or equal to 0 the result is an empty text.

Parameters

Name

Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.

length

Number

length must be a number or a column of Number type.

Example

Function	Result
left("NOWHERE", 3)	NOW

Length

length(param)

Returns the character length of the text parameter.

Parameters

Name

Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.

Example

Function	Result
length("NOWHERE")	7

In

ln(param)

Returns the natural logarithm (base e) of the given parameter.

Parameters

Name

Description

param

Decimal

Parameter must be a number, a numeric column or an expression that returns a number.

Example

Function	Result
ln('1')	0

Log

log(base,param)

Returns the logarithm of parameter given. This function also accepts custom base value for the logarithm.

Parameters

Name

Description

base

Decimal

Indicates the base of the logarithm function. Parameter must be a number, a numeric column or an expression that returns a number.

param

Decimal

Parameter must be a number, a numeric column or an expression that returns a number.

Example

Function	Result
log(10,100)	2

Log10

log10(param)

Returns the logarithm (base 10) of the parameter given.

Parameters

Name

Description

param

Decimal

Parameter must be a number, a numeric column or an expression that returns a number.

Example

Function	Result
log10('10.0')	1

Log2

log2(param)

Returns the logarithm (base 2) of parameter given.

Parameters

Name

Description

param

Decimal

Parameter must be a number, a numeric column or an expression that returns a number.

Example

Function	Result
log2('2.0')	1

Lower

lower(param)

The LOWER function returns the given text with lower case for each character.

Parameters

Name

Description

param

Text

Specifies the Text that needs to be converted with lower case for each character. The parameter can be a text value, a text column or an expression that results in a text value.

Example

Function	Result
lower("Zoho, Your Life's Work, Powered By Our Life's Work!")	zoho, your life's work, powered by our life's work!

Itrim

ltrim(param)

Removes the leading white space characters from the text.

Parameters

Name

Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.

Example

Function	Result
ltrim(" Zoho")	Zoho

Minute

minute(datetime)

Returns the minute component of the timestamp.

Parameters

Name

Description

datetime

Datetime

Specifies a Time or a Datetime column.

Example

Data column
2009-07-30 12:58:59

Function	Result
minute(Data column)	58

Minute of the day

minute_of_day(datetime)

Returns the minute component of the day from the timestamp.

Parameters

Name

Description

datetime

Datetime

Specifies a date-time column.

Example

Data column
2009-07-30 12 58 59

Function	Result
minute_of_day(Data column)	778

Minute of the Hour

minute_of_hour(datetime)

Returns the minute component of an hour from the timestamp.

Parameters

Name

Description

datetime

Datetime

Specifies a date-time column.

Example

Data column
2009-07-30 12 58 59

Function	Result
minute_of_hour(Data column)	58

Millisecond of the Day

millisecond_of_day(datetime)

Returns the millisecond of a day from the timestamp.

Parameters

Name

Description

datetime

Datetime

Specifies a date-time column.

Example

Data column
2009-07-30 00 03 00

Function	Result
millisecond_of_day(Data column)	180000

Millisecond of Second

millisecond_of_second(datetime)

Returns the millisecond of a second from the timestamp.

Parameters

Name

Description

datetime

Datetime

Specifies a date-time column.

Example

Data column
2009-07-30 12 58 59 850

Function	Result
millisecond_of_second(Data column)	850

Is Leap Year

is_leap_year(date)

Returns true for a leap year and false otherwise.

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column .

Example

Data column
2016-04-09

Function	Result
is_leap_year(Date column)	true

Mod

mod(param1,param2)

Returns the remainder after performing numeric division on two numbers.

Parameters

Name

Description

param1

Decimal

Parameter must be a numeric value which is the numerator used in the numeric division.

param2

Decimal

Parameter must be a numeric value which is the denominator used in the numeric division.

Example

Function	Result
mod('7','5')	2

Month

month(date)

Extracts month from the date/timestamp.

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column.

Example

Data column
Nov 04
2016

Function	Result
month(Data column)	11

Month as Name

month_name(date)

Returns the month's name of the date/timestamp.

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column.

Example

Data column
1997-02-28

Function	Result
month_name(Data column)	February

Month and Year

month_year(date)

Returns the month name of the date/timestamp along with the year.

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column.

Example

Data column
1997-02-28

Function	Result
month_year(Data column)	February, 1997

Months in-between

months_between(date1,date2)

Returns the number of months between the two specified dates.

Parameters

Name

Description

date1

Datetime

Specifies a date column or a date-time column from which the difference in months is to be determined.

date2

Datetime

Specifies a date column or a date-time column from which the difference in months is to be determined.

Example

Data column 1
1997-02-28

Data column 2
1997-10-28

Function	Result
months_between('Data column 1', 'Data column 2')	8

Negative

negative(param)

Returns the negated value of the number mentioned in param.

Parameters

Name

Description

param

Decimal

Specifies a number value that is to be negated.

Example

Function	Result
negative('1')	-1

Pi

pi()

Returns the Pi value. The number π is a mathematical constant. It is defined as the ratio of a circle's circumference to its diameter.

Example

Function	Result
pi()	3.1415926536

Power

pow(param1,param2)

Raises a number to the power that is specified by a number.

Parameters

Name

Description

param1

Decimal

Specifies the number which is to be raised to a certain power.

param2

Decimal

Specifies the number which represents the power that the number is to be raised to.

Example

Function	Result
pow('2', '3')	8

Quarter

quarter(date)

Extract quarter of the year, in the range 1 to 4 from the date.

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column.

Example

Data column
2016-08-31

Function	Result
quarter(Data column)	3

Quarter Name

quarter_name(date)

Extract quarter of the year, in the range 1 to 4, with the name of the year from date.

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column.

Example

Data column
2016-08-31

Function	Result
quarter_name(Data column)	Q3

Quarter Year

quarter_year(date)

Extract quarter of the year, in the range 1 to 4, with the year from the date.

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column.

Example

Data column
2016-08-31

Function	Result
quarter_year(Data column)	Q3, 2016

Radians

radians(param)

Converts angle in degrees to radian on the column.

Parameters

Name

Description

param

Decimal

angle in degrees

Example

Function	Result
radians(180)	3.1415926536

Random

random(num1,num2)

Generates a random integer value, between the given numbers, in the column.

Parameters

Name

Description

num1

Decimal

Specifies the starting number for the range from which a random number is returned.

num2

Decimal

Specifies the ending number for the range from which a random number is returned.

Example

Function	Result
random('0', '4')	3

Regex Extract

regexp_extract(str,regexp,groupIdx)

Extracts a group that matches regex expression.

Parameters

Name

Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

regexp

Text

Parameter must be a regular expression pattern passed as a Text.

groupIdx

Number

[Optional] Paramter must be an integer which indicates the index of the group that needs to be extracted.

Example

Function	Result
regexp_extract("100abc", "\\d+" , 0)	100
regexp_extract( "100-abc" , "(\\d+)-([a-zA-Z]+)" , 2)	abc

Regex Replace

regexp_replace(str,regex,replace_with)

Replaces all the subtexts of a text 'str' that matches the regex with the text mentioned in 'replace_with'.

Parameters

Name

Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

regex

Text

Parameter must be a Text specifying the regular expression.

replace_with

Text

Parameter must be a Text specifying the regular expression.

Example

Function	Result
regexp_replace("100-200", "(\\d+)", 'num')	num-num

Replace

replace(str,search,replace)

Replaces all occurrences of search with the provided replacement text.

Parameters

Name

Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

Text

A Text expression. If search is not found in str, str is returned unchanged.

replace

Text

A Text expression. If replace is not specified or is an empty text, nothing replaces the text that is removed from str.

Example

Function	Result
replace('ABCabc', 'abc', 'DEF')	ABCDEF

Reverse

reverse(str)

Returns the reversed version of the given text.

Parameters

Name

Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

Example

Function	Result
reverse('Zoho One')	enO ohoZ

Right

right(str,len)

Returns the rightmost length `len` of characters from the text `str`. If the length is less than or equal to 0 the result is an empty text.

Parameters

Name

Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

len

Number

Specifies the number of characters from the right of the Text to be returned.

Example

Function	Result
right('Zoho One', '3')	One

Round Number

round_num(param,d)

Returns a decimal number rounded to a specified number of decimal places using HALF_EVEN rounding mode.

Parameters

Name

Description

param

Decimal

Specifies the number column or the exact decimal number to be rounded off to the nearest value.

Number

Specifies the number column or the exact number of decimal places that the number should be rounded off to.

Example

Function	Result
round_num('2.5', '0')	2
round_num('5.5', '0')	6
round_num('3.2', '0')	3

rtrim

rtrim(str)

Removes the trailing space characters from the provided text value.

Parameters

Name

Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

Example

Function	Result
rtrim('Zoho One ')	Zoho One

Second

second(datetime)

Returns the seconds component of the date time. The function also supports unix timestamp in milliseconds as the input.

Parameters

Name

Description

datetime

Datetime

Specifies a Time or a Datetime column.

Example

Data column
2009-07-30 12:58:59

Function	Result
second(Data column)	59

Second of the Day

second_of_day(datetime)

Returns the second component of a day from the timestamp. The function also supports unix timestamp in milliseconds as the input.

Parameters

Name

Description

datetime

Datetime

Specifies a date-time column.

Example

Data column
2009-07-30 00 11 00

Function	Result
second_of_day(Data column)	660

SHA1

Syntaxsha(param)

Returns a sha1 hash value as a hex text of the param.

Parameters

Name

Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

Example

Function	Result
sha('Zoho One')	a74457c58f9d3e564bf534d96e1d85d6e513dac4

Soundex

soundex(str)

Returns the Soundex code of the text values in the column.

Parameters

Name

Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

Example

Function	Result
soundex('Miller')	M460

Square Root

sqrt(param)

Returns the square root of the values on the column.

Parameters

Name

Description

param

Decimal

Parameter must be a number or an expression that results in a numeric value or a Number column.

Example

Function	Result
sqrt('64')	8

Substring

substr(sentence,pos,len)

Returns the subtext of the sentence text that starts at a position index and is of a given length.

Parameters

Name

Description

sentence

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

pos

Number

Parameter must be a number representing the index of the starting position of the text to be extracted.

len

Number

[Optional] Parameter must be a number representing the length of the text to be extracted from the 'sentence'

Example

Function	Result
substr('Zoho One', '4', '1')	o

Trim

trim(param)

Removes the leading and trailing space characters from the text values in the column.

Parameters

Name

Description

param

Text

Parameter can be a column of any datatype.

Example

Function	Result
trim(' Zoho One ')	Zoho One

Upper

upper(param)

Returns a text with all of the characters changed to uppercase.

Parameters

Name

Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

Example

Function	Result
upper('Zoho One')	ZOHO ONE

Week of the Year

week_of_year(date,startDay,week_mode)

Returns the week of year (in number) from the date or timestamp (a week is considered to start on a Monday and week 1 is the first week with more than 3 days).

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column.

startDay

Number

[Optional] Specifies the starting day of the week. The value range from 1 to 7 in this order. [1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday]. The default value is 1.

week_mode

Number

[Optional] Week mode determines how the function works. Values allowed are '1' (ISO Week Mode - Minimal 4 days), '2' (week starts from January 1st of the calendar year). The default value is 1.

Example

Data column
06/12/2018

Function	Result
week_of_year(Data column, 1, 1)	49

Week of the Month

week_of_month(date)

Returns the week of the month of the given date.

Parameters

Name

Description

date

Datetime

Specifies the date or a date-time.

Example

Data column
06/13/2018

Function	Result
week_of_month(Data column)	Week 2

Week of the Year with Year

week_of_year_with_year(datetime,startDay,week_mode)

Returns the week of the year with year from the given date

Parameters

Name

Description

datetime

Datetime

Parameter must be a datetime type column

startDay

Number

week_mode

Number

[Optional] Week mode determines how the function works. Values allowed are '1' (ISO Week Mode - Minimal 4 days), '2' (week starts from January 1st of the calendar year). The default value is 1.

Example

Data column
06 Dec 2018
17 Nov 2019

Function	Result
week_of_year_with_year(Data column, 1, 1)	Week 49 2018, Week 46 2019

Year

year(date)

Returns the year from the given date

Parameters

Name

Description

date

Datetime

Parameter must be a date or a datetime column

Example

Data column
06 Dec 2018
17 Nov 2019

Function	Result
year(Data column)	2018, 2019

Century

century(date)

Returns the century of the given date or the date-time input.

Parameters

Name

Description

date

Datetime

Parameter must be of date type or a date-time type.

Example

Data column
06 Dec 2018
17 Nov 2019

Function	Result
century(Data column)	21

Extract Date

extract_date(datetime)

Returns the date part from the given date column, or a date-time column.

Parameters

Name

Description

datetime

Datetime

Parameter must be of datetime type or an expression that returns a datetime.

Example

Data column
2009-03-17 20 18 34 543

Function	Result
extract_date(Data column)	2009-03-17

Extract Time

extract_time(datetime)

Returns the time part from the given date-time column.

Parameters

Name

Description

datetime

Datetime

Parameter must be of datetime type or an expression that returns a datetime.

Example

Data column
2009-05-07 12 03 34

Function	Result
extract_time(Data column)	12:03:34

Add Years

add_years(datetime,num_of_years)

Adds the specified number of years to the given date-time value.

Parameters

Name

Description

datetime

Datetime

Specifies the date-time column to increment.

num_of_years

Decimal

Specifies the number of years by which to increment the timestamp value.

Example

Data column
03/25/2009

Function	Result
add_years(Data column, '3')	03/25/2012

Sub years

sub_years(datetime,num_of_years)

Subtracts the specified number of years from the given date-time value.

Parameters

Name

Description

datetime

Datetime

Specifies the date-time column or the exact date to decrement.

num_of_years

Decimal

Specifies the number of years by which to decrement the timestamp value.

Example

Data column
06/12/2018

Function	Result
sub_years(Data column, '3')	06/12/2015

Difference in Years

diff_in_years(startDate,endDate)

Returns the number of years between startDate and endDate.

Parameters

Name

Description

startDate

Datetime

Specifies a date column or a date-time column, the exact start date or an expression that results in a date value.

endDate

Datetime

Specifies a date column or a date-time column, the exact end date or an expression that results in a date value.

Example

Data column 1
2013-08-10

Data column 2
2009-03-07

Function	Result
diff_in_years(Data column 1, Data column 2)	4

Add Weeks

add_weeks(datetime,num_of_weeks)

Adds the specified number of weeks to the given date-time value.

Parameters

Name

Description

datetime

Datetime

Specifies the date-time column to increment.

num_of_weeks

Decimal

Specifies the number of weeks by which to increment the timestamp value.

Example

Data column
03/25/2009

Function	Result
add_weeks(Data column, '2')	04/08/2009

Sub Weeks

sub_weeks(datetime,num_of_weeks)

Subtracts the specified number of weeks in the given date-time value.

Parameters

Name

Description

datetime

Datetime

Specifies the date-time column or the exact date to decrement.

num_of_weeks

Decimal

Specifies the number of weeks by which to decrement the timestamp value.

Example

Data column
06/12/2018

Function	Result
sub_weeks(Data column, '2')	11/22/2018

Difference in Weeks

diff_in_weeks(startDate,endDate)

Returns the number of weeks between startDate and endDate.

Parameters

Name

Description

startDate

Datetime

Specifies a date column or a date-time column, the exact start date or an expression that results in a date value.

endDate

Datetime

Specifies a date column or a date-time column, the exact end date or an expression that results in a date value.

Example

Data column 1
2009-08-10

Data column 2
2009-09-17

Function	Result
diff_in_weeks(Data column 1, Data column 2)	7

Add Months

add_months(date,num_of_months)

Adds the specified number of months to the specified date value

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column

num_of_months

Decimal

Specifies the number of months to add to the date value

Example

Data column
2009-11-25

Function	Result
add_months(Data column, `3`)	2010-02-25

Sub Months

sub_months(datetime,num_of_months)

Subtracts the specified number of months from the given date value.

Parameters

Name

Description

datetime

Datetime

Specifies a date column or a date-time column.

num_of_months

Decimal

Specifies the number of months by which to decrement the date.

Example

Data column
19/12/2016

Function	Result
sub_months(Data column, '3')	19/09/2016

Difference in Months

diff_in_months(startDate,endDate)

Returns the number of months between startDate and endDate.

Parameters

Name

Description

startDate

Datetime

Specifies a date column or a date-time column.

endDate

Datetime

Specifies a date column or a date-time column.

Example

Data column 1
2009-08-17

Data column 2
2009-03-10

Function	Result
diff_in_months(Data column 1, Data column 2)	5

Add Days

add_days(date,num_of_days)

Adds the specified number of days to the specified date value.

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column to increment.

num_of_days

Decimal

Specifies the number of days by which to increment the dates.

Example

Data column
12/25/2009

Function	Result
add_days(Data column, `30`)	01/24/2010

Sub Days

sub_days(date,num_of_days)

Subtracts the specified number of days from the specified date value.

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column to decrement.

num_of_days

Decimal

Specifies the number of days by which to decrement the date.

Example

Data column
2009-12-25

Function	Result
sub_days(Data column, '5')	2009-12-20

Difference in Days

diff_in_days(startDate,endDate)

Returns the number of days between startDate and endDate.

Parameters

Name

Description

startDate

Datetime

Specifies a date column or a date-time column.

endDate

Datetime

Specifies a date column or a date-time column.

Example

Data column 1
2009-03-10

Data column 2
2009-03-07

Function	Result
diff_in_days(Data column 1, Data column 2)	3

Add Hours

add_hours(datetime,num_of_hours)

Adds the specified number of hours to the given Time column or a Datetime column.

Parameters

Name

Description

datetime

Datetime

Specifies the Datetime column to increment.

num_of_hours

Decimal

Specifies the number of hours by which the value will be incremented.

Example

Data column
12/08/2018 07:58:59

Function	Result
add_hours(Data column, '3')	12/08/2018 10:58:59

Sub Hours

sub_hours(datetime,num_of_hours)

Subtracts the specified number of hours from the given date value.

Parameters

Name

Description

datetime

Datetime

Specifies the date-time column to decrement.

num_of_hours

Decimal

Specifies the number of hours by which to decrement the timestamp value.

Example

Data column
1965-07-27 11 58 59

Function	Result
sub_hours(Data column, 3)	1965-07-27 8:58:59

Difference in Hours

diff_in_hours(startDateTimestamp,endDateTimestamp)

Returns the number of hours between startDate timestamp and endDate timestamp.

Parameters

Name

Description

startDateTimestamp

Datetime

Specifies a date-time column.

endDateTimestamp

Datetime

Specifies a date-time column.

Example

Data column 1
2009-03-07 23 20 40 600

Data column 2
2009-03-07 20 18 34 543

Function	Result
diff_in_hours(Data column 1, Data column 2)	3

Add Minutes

add_minutes(datetime,num_of_minutes)

Adds the specified number of minutes to the given Time or a Datetime value.

Parameters

Name

Description

datetime

Datetime

Specifies the Time or a Datetime column to increment.

num_of_minutes

Decimal

Specifies the number of minutes by which to increment the timestamp value.

Example

Data column
12/08/2018 11:58:59

Function	Result
add_minutes(Data column, '2')	12/08/2018 12:00:59

Sub Minutes

sub_minutes(datetime,num_of_minutes)

Subtracts the specified number of minutes from the given date-time value.

Parameters

Name

Description

datetime

Datetime

Specifies the date-time column to decrement.

num_of_minutes

Decimal

Specifies the number of minutes by which to decrement the timestamp value.

Example

Data column
2018-07-18 00 10 00

Function	Result
sub_minutes(Data column, 2)	2018-07-18 00:08:00

Difference in Minutes

diff_in_minutes(startDateTimestamp,endDateTimestamp)

Returns the number of minutes between startDate timestamp and endDate timestamp.

Parameters

Name

Description

startDateTimestamp

Datetime

Specifies a date-time column.

endDateTimestamp

Datetime

Specifies a date-time column.

Example

Data column 1
2009-03-07 20 20 34 600

Data column 2
2009-03-07 20 18 34 543

Function	Result
diff_in_minutes(Data column1, Data column 2)	2

Add Seconds

add_seconds(datetime,num_of_seconds)

Adds the specified number of seconds to the given Time or a Datetime value.

Parameters

Name

Description

datetime

Datetime

Specifies the Time or a Datetime column to increment.

num_of_seconds

Decimal

Specifies the number of seconds by which to increment the timestamp value.

Example

Data column
03/25/2009 11:58:49

Function	Result
add_seconds(Data column, '10')	03/25/2009 11:58:59

Sub Seconds

sub_seconds(datetime,num_of_seconds)

Subtracts the specified number of seconds in the given date-time value.

Parameters

Name

Description

datetime

Datetime

Specifies the date-time column to decrement.

num_of_seconds

Decimal

Specifies the number of seconds by which to decrement the timestamp value.

Example

Data column
06/12/2018 00 03 00

Function	Result
sub_seconds(Data column, '10')	06/12/2018 00:02:50

Difference in Seconds

diff_in_seconds(startDateTimestamp,endDateTimestamp)

Returns the number of milliseconds between startDate timestamp and endDate timestamp.

Parameters

Name

Description

startDateTimestamp

Datetime

Specifies a date-time column.

endDateTimestamp

Datetime

Specifies a date-time column.

Example

Data column 1
2009-03-07 20 20 36 600

Data column 2
2009-03-07 20 20 34 543

Function	Result
diff_in_seconds(Data column 1, Data column 2)	02

Invert Case

invert_case(param)

Returns the text with all case inverted i.e. upper case is converted to lower case and vice versa.

Parameters

Name

Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

Example

Function	Result
invert_case('Zoho One is an Operating System for your Business')	zOHO oNE IS AN oPERATING sYSTEM FOR YOUR bUSINESS

Alternate Case

alternate_case(string)

Returns a text with alternate lower and upper case for each character of the input text starting with lower case for the first character.

Parameters

Name

Description

string

Text

Specifies the Text that needs to be converted with alternate case for each character.

Example

Function	Result
alternate_case("Zoho, Your Life's Work, Powered By Our Life's Work!")	zOhO, yOuR LiFe's wOrK, pOwErEd bY OuR LiFe's wOrK!

Sentence Case

sentence_case(param)

Returns a text of the values in the columns after applying the sentence case to it.

Parameters

Name

Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

Example

Function	Result
sentence_case('welcome to the united states of america')	"Welcome to the united states of america"

Camel Case

camel_case(string)

Returns the text after applying the camel case to it.

Parameters

Name

Description

string

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

Example

Function	Result
camel_case("Zoho one is an operating system for your business")	ZohoOneIsAnOperatingSystemForYourBusiness

Title Case

title_case(param)

Returns a text of the values in the columns after applying the title case to it. Title case means that the first letter of each word is capitalized, except for certain small words, such as articles and short prepositions

Parameters

Name

Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

Example

Function	Result
title_case('united states of america')	United States of America

Lines to Space

lines_to_space(param)

Returns a text value where the new lines are converted to spaces.

Parameters

Name

Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.

Example

Function	Result
lines_to_space('Zoho\nOne')	Zoho One

Space to Lines

space_to_lines(param)

Returns a text value where the spaces are converted to new lines.

Parameters

Name

Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.

Example

Function	Result
space_to_lines('Zoho One')	Zoho\nOne

Tab to Space

tab_to_space(param)

Returns a text value where the tabs are converted to spaces.

Parameters

Name

Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

Example

Function	Result
tab_to_space('Zoho\tOne')	Zoho One

Space to Tab

space_to_tab(param)

Returns a text value where the spaces are converted to tabs.

Parameters

Name

Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

Example

Function	Result
space_to_tab('Zoho One')	Zoho\tOne

To Single Space

to_single_space(param)

Returns a text with multiple spaces into a single space.

Parameters

Name

Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

Example

Function	Result
to_single_space('Zoho Office Suite')	Zoho Office Suite

To Single Tab

to_single_tab(param)

Returns a text with multiple tab spaces into a sinlge tab space.

Parameters

Name

Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

Example

Function	Result
to_single_tab('Zoho\t\t\tOffice\tSuiteSQL\tOrg')	Zoho\tOffice\tSuiteSQL\tOrg

Remove Empty Lines

remove_empty_lines(param)

Removes all the empty lines in the column

Parameters

Name

Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.

Example

Function	Result
remove_empty_lines('Zoho\n\n\nOffice\n\nSuite')	Zoho\nOffice\nSuite

Truncate Character

truncate_char(param1,param2)

Truncates a given number of characters from the input text value.

Parameters

Name

Description

param1

Text

Parameter can be a column of any datatype.

param2

Number

Specifies the number of characters to be removed from the Text. The value of the parameter should be smaller than the length of the Text.

Example

Function	Result
truncate_char(Zoho One, 4)	Zoho

Truncate Words

truncate_words(param1,param2)

Truncates a given number of words from the input text value.

Parameters

Name

Description

param1

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

param2

Number

Specifies the number of words to be removed from the Text. The value of the parameter should be smaller than the number of words in the Text and greater than 0.

Example

Function	Result
truncate_words('Zoho One', 1)	Zoho

Remove Space

remove_space(str)

Removes all the spaces in the column.

Parameters

Name

Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

Example

Function	Result
remove_space(' Zoho One ')	"ZohoOne"

To Unix Timestamp

to_unix_timestamp(date)

Returns the UNIX timestamp of the given date time values.

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column.

Example

Data column
06/12/2018 00 03 00

Function	Result
to_unix_timestamp(Data column)	1544034780

Is Missing

is_missing(param)

Returns true for missing values, false otherwise.

Parameters

Name

Description

param

Text

Parameter can be a column of any datatype.

Example

Sales
11
12
' '
67

Function	Result
is_missing(Sales)	[false, false, true, false]

Is Valid

is_valid(param1,param2)

Returns true for valid values; false otherwise.

Parameters

Name

Description

param1

Text

Parameter must be a column conforming to any one data type.

param2

Text

Data type to verify if param1 is valid. It can be one of these values: text, date, number, datetime, decimal, currency, length, temperature, boolean, email, URL, list, map and custom data types

Example

Column1
"ABC"
"DEF"
"GHI"

Function	Result
is_valid(`Column1`,'text')	TRUE

Is Invalid

is_invalid(param1,param2)

Returns true for invalid values; false otherwise.

Parameters

Name

Description

param1

Text

Parameter must be a column conforming to any one data type.

param2

Text

Data type to verify if param1 is invalid. It can be one of these values: text, date, number, datetime, decimal, currency, length, temperature, boolean, email, URL, list, map and custom data types

Example

Column1
"ABC"
"DEF"
"GHI"

Function	Result
is_invalid(`Column1`, 'Number')	TRUE

Size

size(param)

Returns the size of a list. Returns -1 if null.

Parameters

Name

Description

param

List

Parameter must be a column of type List, or a group of values as a list.

Example

Function	Result
size(['b', 'd', 'c', 'a'])	4

List Concat

list_concat(param1,param2)

Returns a list that contains all the elements in the first list followed by all the elements in the second. The two input lists must contain items in the same data structure format.

Parameters

Name

Description

param1

List

Specifies the first of the two lists to concatenate.

param2

List

Specifies the second of the two lists to concatenate.

Example

column1
[1,2,3,4,5]

column2
["A","B,"C","D","E" ]

Function	Result
list_concat(column1, column2)	["1","2","3","4","5","A","B","C","D","E"]

List Unique

list_unique(param)

Filters the list by removing duplicate entries from it. Returns the filtered list.

Parameters

Name

Description

param

List

Parameter must be of type list or a List column.

Example

list
[30, 40, 50, 30, 70, 30, 90]

Function	Result
list_unique(list)	["40", "50", "70", "90"]

List Intersect

list_intersect(param1,param2)

This function compares the values of two lists, and returns only the matching elements. The two lists must contain items in the same data structure format.

Parameters

Name

Description

param1

List

The list to compare from.

param2

List

A list to compare against.

Example

list1
[1,2,3,4,5]

list2
["A", 1, "B" ,2, "C", 3, "D", 4, "E"]

Function	Result
list_intersect(list1, list2)	["1", "2", "3", "4"]

List Zip

list_zip(param1,param2)

Converts two unidimensional list columns into a multi dimensional list.

Parameters

Name

Description

param1

List

Parameter must be of type list or a List column.

param2

List

Parameter must be of type list or a List column.

Example

list1
["A","B","C"]

list2
["D","E","F"]

Function	Result
list_zip(list1 , list2)	[["A","D"],["B","E"],["C","F"]]

Split

split(str,regex)

Splits the text around occurrences that match the regular expression.

Parameters

Name

Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

regex

Text

Parameter must be a Text mentioning the regular expression that returns a Text.

Example

Function	Result
split('oneAtwoBthreeC', '[ABC]')	["one","two","three",""]

Collect Set

collect_set(param)

Collects the distinct values from a column as a list.

Parameters

Name

Description

param

Text

Parameter must be a column conforming to any one data type.

Example

Column1
"Australia"
"United States"
"Australia"
"Canada"
"Canada"

Function	Result
collect_set(Column1)	["Australia","United States","Canada"]

Contains

contains(string1,string2,num)

Returns true if the text string1 contains string2

Parameters

Name

Description

string1

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

string2

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

num

Number

Parameter must be a numeric value. Using '0' will make the search case-sensitive in finding the Text. Using any other number will render the search case-insensitive.

Example

Function	Result
contains("Pineapple", "Apple", 0)	True

Starts With

starts_with(string1,string2,number)

Compares two text columns and returns true if the text string1 starts with string2.

Parameters

Name

Description

string1

Text

Specifies Text values or a Text column.

string2

Text

Specifies Text values or a Text column

number

Number

Specifies if the comparison should be case-sensitive (input '1') or non case-sensitive (input '0').

Example

Function	Result
starts_with('Sunday', 'Sun', '1')	True
starts_with('Sunday', 'Mon', '0')	False

Ends With

ends_with(string1,string2,num)

Returns true if the text string1 ends with string2.

Parameters

Name

Description

string1

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.

string2

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.

num

Number

Parameter must be a numeric value. Using '0' will make the search case-sensitive in finding the sting. Using any other number will render the search case-insensitive.

Example

Function	Result
ends_with("Newyork","york", 0)	True

Extract Date With Format

extract_date_with_format(datetime,format)

Returns the date component from date column in the given format.

Parameters

Name

Description

datetime

Date

Parameter must be a date column or a date-time column.

format

Text

Specifies the format in which the date should be extracted by the function.

Example

Data column
2009-05-17

Function	Result
extract_date_with_format(Data column, 'dd-MM-yyyy')	17-05-2009

Extract Time With Format

extract_time_with_format(datetime,format)

Returns the time component from datetime column in the given format.

Parameters

Name

Description

datetime

Datetime

Parameter is mandatory and it must be a date-time type column.

format

Text

Parameter is mandatory and it must be a Text representing the time format to extract.

Example

Data column
2009-05-07 12 03 34 433

Function	Result
extract_time_with_format(Data column, 'hh:mm:ss:SSS')	12:03:34:433

Keyword Extraction

keyword_extraction(string,maxKeywordLength,topElements,model)

Returns the keywords present in the column.

Parameters

Name	Description
string Text	Parameter must be a Text or a Text column, or an expression that returns a Text.
maxKeywordLength Number	Maximum number of words in a single keyword set. For example, for the sentence, 'Operating Systems' key length can be at most two.
topElements Number	Pick top N elements from results.
model Text	Three models are supported. They are namely, ONLY_VALID_KEYWORDS, RANKED_KEYWORDS, RANKED_KEYWORDS_WITH_INDEX.

Example

Function	Result
keyword_extraction(`A room without books is like a body without a soul.` , '1' , '4' , 'ONLY_VALID_KEYWORDS')	('room','books','body','soul')

Language Prediction

lang_predict(string,langType)

Detect language from the given text. Supports 71 languages.

Parameters

Name

Description

string

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

langType

Text

Parameter must be either be 'code', or 'name'. Code will display the language code as a result, whereas name will display the name of the language.

Example

Function	Result
lang_predict('மதிப்புரைகளைப் படிக்கவும்' , 'name')	Tamil
lang_predict('மதிப்புரைகளைப் படிக்கவும' , 'code')	ta

Min If

min_if(num,condition)

Returns the min value on satisfying the given condition

Parameters

Name

Description

num

Decimal

Parameter must be a Number column

condition

Boolean

Condition must return a Boolean value based on which the function is calculated

Example

ticket_count
30
40
50
60
70
80
90

Function	Result
min_if(ticket_count, ticket_count!=30)	40

Max If

max_if(num,condition)

Returns the max value on satisfying the given condition

Parameters

Name

Description

num

Decimal

Parameter must be a Number column

condition

Boolean

Condition must return a Boolean value based on which the function is calculated

Example

ticket_count
30
40
50
60
70
80
90

Function	Result
max_if(ticket_count, ticket_count!=90)	80

Avg If

avg_if(num,condition)

Returns the average value on satisfying the given condition

Parameters

Name

Description

num

Decimal

Parameter must be a Number column

condition

Boolean

Condition must return a Boolean value based on which the function is calculated

Example

ticket_count
30
40
50
60
70
80
90

Function	Result
avg_if(ticket_count, ticket_count!=30)	65

Sum If

sum_if(num,condition)

Returns the sum of the values on satisfying the given condition

Parameters

Name

Description

num

Decimal

Parameter is mandatory and must be a numeric column or value

condition

Boolean

Condition must return a Boolean value based on which the function is calculated

Example

marks
[90, 85, 95, 65]

grade
[A, B, A, D]

Function	Result
sum_if(marks, `grade`=='A')	[185, 185, 185, 185]

Mode If

mode_if(num,condition)

Returns the mode value on satisfying the given condition

Parameters

Name

Description

num

Decimal

Parameter must be a Number column

condition

Boolean

Condition must return a Boolean value based on which the function is calculated

Example

ticket_count
30
40
30
40
30
80
90

Function	Result
mode_if(ticket_count, ticket_count!=30)	40

Standard Deviation If

stddev_if(number,condition)

Returns the population standard deviation of the given column after applying the specified condition.

Parameters

Name

Description

number

Decimal

Parameter must be a numeric column's value or an expression that returns a number.

condition

Boolean

Condition must return a boolean value based on which the function is calculated.

Example

Country
India
USA
Germany

Movie
Avatar
Inception
Avatar

Rating
9.432
1.0
2.0

Function	Result
stddev_if(Rating, `Movie`=='Avatar')	5.2552175978

Variance If

variance_if(num,condition)

Returns the variance of the values on satisfying the given condition

Parameters

Name

Description

num

Decimal

Parameter is mandatory and must be a numeric column or an expression that returns a list of numbers

condition

Boolean

Condition must return a Boolean value based on which the function is calculated

Example

sales_amount
9.432
1
2

country
India
USA
India

Function	Result
variance_if(sales_amount, 'country'=='India');	27.6173124, 27.617312, 27.617312

Count If

count_if(col,condition)

Returns the total number of values on satisfying the given condition

Parameters

Name

Description

col

Text

Parameter must be a column

condition

Boolean

Condition must return a Boolean value based on which the function is calculated

Example

ticket_count
30
40
50
60
70
80
90

Function	Result
count_if(ticket_count, ticket_count!=30)	6

Count Distinct

count_distinct(col)

Returns the total number of distinct values in the column

Parameters

Name

Description

col

Text

Parameter must be a column

Example

ticket_count
30
40
30
60
70
60
90

Function	Result
count_distinct(ticket_count);	5

Count Distinct If

count_distinct_if(col,condition)

Returns the total number of distinct values satisfying the given condition

Parameters

Name

Description

col

Text

Parameter must be a column

condition

Boolean

Condition must return a Boolean value based on which the function is calculated

Example

ticket_count
30
40
50
60
30
80
40

Function	Result
count_distinct_if(ticket_count, ticket_count!=30)	4

Range

range(startNum,stopNum,stepNum)

Computes an array of integers beginning from 'startNum' to 'stopNum' having an interval of 'stepNum' between consecutive integers in the range. If the function generates more than 1000 values for a cell, the output is a null value and the 'stopNum' is excluded from the output. 'StepNum' must always be a positive integer. If 'startNum' is greater than 'stopNum', steps are automatically converted to negative values and the output will be a decrement range.

Parameters

Name

Description

startNum

Number

Parameter must be a Number column or a number. Decimal values are not supported.

stopNum

Number

Parameter must be a Number column or a number. Decimal values are not supported.

stepNum

Number

Specifies the step value for the range. Decimal values are not supported.

Example

Function	Result
range('2','14','2')	[2, 4, 6, 8, 10, 12]
range('14','2','2')	[14, 12, 10, 8, 6, 4]

Locate

locate(string1,string2,position)

Returns the position of the first occurrence of the text 'string1' in 'string2' after the 'position' given

Parameters

Name

Description

string1

Text

Parameter must be a Text, a Text column or an expression that returns a Text

string2

Text

Parameter must be a Text, a Text column or an expression that returns a Text

position

Number

[Optional] Specifies the position after which 'string2' should be located.

Example

Function	Result
locate('bar', 'foobarbar')	4
locate('bar', 'foobarbar' , 5)	5

Lpad

lpad(string,length,string2)

Returns the 'string' text left-padded with a 'padString' for the 'length' specified. Padding will be applied only when 'length' value is more than the length of the text, if not the output text is shortened to the 'length' parameter.

Parameters

Name

Description

string

Text

Parameter must be a Text, a Text column or an expression that returns a Text

length

Number

Specifies the total length of the output Text

string2

Text

Specifies the Text that must be added to, or replaced in the 'Text1' value

Example

Function	Result
lpad('abcd', 3, 'A')	Aab
lpad('abcd', 7, 'A')	AAAabcd

Rpad

rpad(string,length,string2)

Returns the 'string' text right-padded with a 'padString' for the 'length' specified. Padding will be applied only when 'length' value is more than the length of the text, if not the output text is shortened to the 'length' parameter.

Parameters

Name

Description

string

Text

Parameter must be a Text column or an expression that returns a Text

length

Number

Specifies the total length of the output Text

string2

Text

Specifies the Text that must be added to, or replaced in the 'Text1' value

Example

Function	Result
rpad('abcd', 5, 'A')	abcdA
rpad('abcd', 7, 'A')	abcdAAA

Next as List

nest_as_list(params)

Converts the given parameters to a list

Parameters

Name

Description

params

Text

All parameters must be a Text, or a Text column, or an expression that returns a Text

Example

Function	Result
nest_as_list('Arjun', 'Zip code: 308-242-0670', 'Status: New resident')	["Arjun", "Zip code: 99501", "Status: New resident"]

Nest as Map

nest_as_map(params,...)

Converts the given parameters to a map. The first value will be the key, and the second will be the value in making each key-value pair.

Parameters

Name

Description

params

Multiple

All parameters must be a column

Example

details
['Name', 'Arjun', 'Zip code', '308-242-0670']

Function	Result
nest_as_map(details)	{'Name' : 'Arjun', 'Zip code' : '308-242-0670'}

ELT

elt(num,string,...)

Returns the text value available at the index number specified in the first argument

Parameters

Name

Description

num

Number

Parameter must be a numerical value, a numerical column or an expression that returns a number

string

Multiple

All parameters must be a Text column or an expression that returns a Text

Example

Function	Result
elt('5', 'Zoho', 'is the', 'operating system', 'for your', 'business')	business

Field

field(string1,string2,...)

Searches the value in the first argument with all the rest of the arguments and returns the position of the argument where the first match is found

Parameters

Name

Description

string1

Text

First parameter must be a Text, a Text column or an expression that returns a Text

string2

Multiple

Each parameter from the second must be a Text, a Text column or an expression that returns a Text

Example

Function	Result
field('as','has','as','have')	2

Insert

insert(string1,position,length,string2)

Inserts the text string2 in string1 at the index 'position' for the specified 'length'

Parameters

Name	Description
string1 Text	Parameter must be a Text, a Text column or an expression that returns a Text
position Number	The position where the 'Text2' parameter must be inserted
length Number	The length of the Text
string2 Text	Specifies the Text that must be added to, or replaced in the 'Text1' value

Example

Function	Result
insert('abcddb', 3, 2, 'efgh')	abefghdb

Repeat

repeat(string,num)

Repeats the given 'string' text for the specified number of times and returns the constructed text.

Parameters

Name

Description

string

Text

Parameter must be a Text column or a Text, or an expression that returns a Text

num

Number

Parameter must be a numerical value, a numerical column or an expression that returns a number. Value must be less than or equal to 9999.

Example

Function	Result
repeat('Abcd', 3)	AbcdAbcdAbcd

Percentile

percentile(num,percentage,frequency)

Returns the exact percentile value of 'num' at the given 'percentage'

Parameters

Name

Description

num

Decimal

Parameter must be a Number column

percentage

Text

Parameter must be a Number column or a number, or an expression that returns a value between 0.0 to 1.0

frequency

Number

[Optional] Parameter must be a Number column or a number, or an expression that returns a positive number

Example

ticket_count
30
40
50
60
70
80
90

Function	Result
percentile(ticket_count, 0.3, 0)	3.0

Acos

acos(num)

Returns the inverse cosine or arc cosine of the input expressed in radians

Parameters

Name

Description

num

Decimal

Parameter must be a numeric value, a numeric column or an expression that returns a number in the range of [-1...1]

Example

Function	Result
acos(0.34);	1.22387943

Asin

asin(num)

Returns the arc sine or inverse sine of the input expressed in radians

Parameters

Name

Description

num

Decimal

Parameter must be a number, or a numeric column, or an expression that returns a number in the range of [-1...1]

Example

Function	Result
asin(0.34);	19.876874

Atan

atan(num)

Returns the arc tangent or inverse tangent of the input expressed in radians

Parameters

Name

Description

num

Decimal

Parameter must be a number, or a numeric column, or an expression that returns a number in the range of [-1...1]

Example

Function	Result
atan(0.34);	18.77803322

Atan

atan2(num1,num2)

Returns arc tangent or inverse tangent for two numeric values

Parameters

Name

Description

num1

Decimal

The parameter must be a number, or numeric columns, or expressions that returns a number

num2

Decimal

The parameter must be a number, or numeric columns, or expressions that returns a number

Example

Function	Result
atan2(5, 5);	0.7853981633974483

Cos

cos(num)

Returns the trigonometric cosine of the given angle in radians.

Parameters

Name

Description

num

Decimal

Parameter must be a numeric value, a numeric column or an expression that returns a number

Example

Function	Result
cos(0);	1.0

Sin

sin(num)

Returns the trigonometric sine of the given angle in radians.

Parameters

Name

Description

num

Decimal

Parameter must be a number, or a numeric column, or an expression that returns a number

Example

Function	Result
sin(0)	0.0

Tan

tan(angle)

Returns the trigonometric tangent of a given angle in radians.

Parameters

Name

Description

angle

Decimal

Parameter must be a number, or a numeric column, or an expression that returns a number

Example

Function	Result
tan(9.432)	0.0072221648

Cot

cot(num)

Returns the trigonometric cotangent of a given angle in radians.

Parameters

Name

Description

num

Decimal

Parameter must be a numeric value, a numeric column, or an expression that returns a number

Example

Function	Result
cot(0.25)	3.916317

Sign

sign(number)

Detects whether the value in the numeric column is positive, negative or zero

Parameters

Name

Description

number

Decimal

Specifies the number column for which the function is to be applied

Example

Function	Result
sign('-24')	-1

Is Last NDayis_last_nday(date,num)

Returns true if the given date value is within the previous N days from today

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column

num

Number

Specifies the number of days

Example

Data column
06/15/2020

Function	Result
is_last_nday(Data column, 10)	false

Is Last NMonthis_last_nmonth(date,num)

Returns true if the given date value is within the previous N months from the current month

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column

num

Number

Specifies the number of months

Example

Data column
05/15/2020

Function	Result
is_last_nmonth(Data column, 3)	false

Is Last NQuarter

is_last_nquarter(date,num)

Returns true if the given date value is within the previous N quarters from the current quarter

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column

num

Number

Specifies the number of quarters

Example

Data column
05/15/2020

Function	Result
is_last_nquarter(Data column, 3)	false

Is Last NYear

is_last_nyear(date,num)

Returns true if the given date value is within the previous N years from the current year

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column

num

Number

Specifies the number of years

Example

Data column
06/15/2019

Function	Result
is_last_nyear(Data column, 10)	false

Is Current Month

is_current_month(date)

Returns true if the given date or the date-time value falls under the current month.

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column.

Example

Data column
06/15/2020

Function	Result
is_current_month(Data column)	false

Is Current Quarter

is_current_quarter(date)

Returns true if the given date or the date-time value falls under the current quarter.

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column.

Example

Data column
07/15/2000

Function	Result
is_current_quarter(Data column)	false

Is Current Week

is_current_week(date,startDay)

Returns true if the given date or the date-time value falls under the current week. The starting day of the week is Sunday by default. This value can be changed using the 'startDay' parameter.

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column.

startDay

Number

Specifies the starting day of the week. The value range from 0 to 6 in this order. [0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday]

Example

Data column
06/15/2020

Function	Result
is_current_week(Data column, 1)	false

Is Current Year

is_current_year(date)

Returns true if the given date or datetime value falls under the current year.

Parameters

Name

Description

date

Datetime

Specifies a date column or a datetime column.

Example

Data column
07/15/2000

Function	Result
is_current_year(Data column)	false

Is Next NDay

is_next_nday(date,num)

Returns true if the given date value falls in the next N days from today

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column

num

Number

Specifies the number of days

Example

Data column
06/18/2020

Function	Result
is_next_nday(Data column, 10)	false

Is Next NMonth

is_next_nmonth(date,num)

Returns true if the given date value falls under the next N months from this month

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column

num

Number

Specifies the number of months

Example

Data column
06/18/2020

Function	Result
is_next_nmonth(Data column, 3)	false

Is Next NQuarter

is_next_nquarter(date,num)

Returns true if the given date value falls under the next N quarters from this quarter

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column

num

Number

Specifies the number of quarters

Example

Data column
06/18/2020

Function	Result
is_next_nquarter(Data column, 1)	false

Is Next NYear

is_next_nyear(date,num)

Returns true if the given date value falls under the next N years from this year

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column

num

Number

Specifies the number of years

Example

Data column
06/15/2020

Function	Result
is_next_nyear(Data column, 2)	false

Start Date

start_date(date,unit)

Returns start date for the given date value using the given date units

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column.

unit

Text

Specifies the unit of the date column. The date units can be 'week', 'week_sunday', 'week_monday', 'month', 'quarter' and 'year'.

Example

Data column
28/10/2020

Function	Result
start_date(Data column, 'month')	01/10/2020

End Date

end_date(date,units)

Returns the end date for the given date value using the given date units.

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column over which the function is applied

units

Text

Specifies the unit of the date column. The date units can be 'week', 'week_sunday', 'week_monday', 'month', 'quarter' and 'year'.

Example

Data column
2018-08-27

Function	Result
end_date(Data column, 'month')	2018-08-31

ASCII

ascii(string)

Returns the ASCII value of the specified character

Parameters

Name

Description

string

Text

Parameters must be a Text or a Text column, or an expression that returns a Text

Example

Function	Result
ascii('A')	65

Bit Length

bit_length(string)

Returns the value of the length of the text parameter as bit length

Parameters

Name

Description

string

Text

Parameters must be a Text, or a Text column, or an expression that returns a Text

Example

Function	Result
bit_length("AA")	16

Index Of

index_of(string1,string2)

Returns the index of the first occurrence of the text 'string2' in 'string1'

Parameters

Name

Description

string1

Text

The parameter must be a Text, a Text column, or an expression that returns a Text

string2

Text

The parameter must be a Text, a Text column, or an expression that returns a Text

Example

Function	Result
index_of('abcddb','db')	5

Space

space(number)

Returns a text consisting of n spaces.

Parameters

Name

Description

number

Number

Specifies number of spaces required. Parameter can be a number column specifying the number of spaces.

Example

Function	Result
space('2')	' '

Unhex

unhex(HexVal)

Returns the corresponding character for each pair of hexadecimal digits

Parameters

Name

Description

HexVal

Text

Parameter must be a hexadecimal Text value

Example

Function	Result
unhex('476F64');	God

Tomorrow

tomorrow()

Returns the date after the current date

Example

Function	Result
tomorrow();	02/06/2020

Yesterday

yesterday()

Returns the date of the previous day from the current date

Example

Function	Result
yesterday();	28/05/2020

Year and Week

year_week(date)

Returns the year and week of the given date

Parameters

Name

Description

date

Datetime

Parameter must be a date column or a date-time column

Example

Data column
06 Dec 2018
17 Nov 2018

Function	Result
year_week(Data column);	201848, 201846

String Compare

string_compare(string1,string2)

Compares two text columns and returns 0 if the columns are of equal size, 1 if column 1 is bigger than column 2 and returns -1 if column 2 is bigger than column 1.

Parameters

Name

Description

string1

Text

Specifies Text values or a Text column

string2

Text

Specifies Text values or a Text column

Example

Function	Result
string_compare('Sunday', 'Monday')	0

Correlation

correl(numList1,numList2)

Returns the Pearson Coefficient of correlation between two numerical columns

Parameters

Name

Description

numList1

Decimal

parameter must be a number column

numList2

Decimal

parameter must be a number column

Example

Age
[20, 21, 24, 45, 46, 54, 60, 61, 63]

Weight
[123, 145, 160, 155, 200, 150, 134, 133, 120]

Function	Result
correl('Age', 'Weight');	-0.1316

First Date of the Current Week

first_date_current_week(date,startDay)

Returns the day the current week begins with

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column.

startDay

Number

Specifies the starting day of the week. The value range from 0 to 6 in this order. [0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday]

Example

Data column
11 Oct 2013

Function	Result
first_date_current_week(`Data column`, 4)	09 Oct 2013

Is Previous Week

is_previous_week(date,startDay)

Returns true if the given date value falls in the previous week based on the start day of the week

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column

startDay

Number

Specifies the starting day of the week. The value range from 0 to 6 in this order. [0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday]

Example

Data column
10 Nov 2100

Function	Result
is_previous_week(`Data column`, 0)	false

Is Next Week

is_next_week(date,startDay)

Returns true if the given date value falls in the next week based on the start day of the week

Parameters

Name

Description

date

Datetime

Specifies a date column or a date-time column

startDay

Number

Specifies the starting day of the week. The value range from 0 to 6 in this order. [0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday]

Example

Data column
20 Nov 1900

Function	Result
is_next_week(`Data column`, 1)	false

Current Time

current_time()

Returns the current time. Function accepts no parameters.

Example

Function	Result
current_time()	19:18:55

Parse Date

parse_date(date,outputFormat,inputFormats,...)

Returns date in the required format if the given value falls under any of the given formats

Parameters

Name

Description

date

Text

Specifies a column with dates in different formats

outputFormat

Text

Specifies the date format that the given date should be parsed into

inputFormats

Multiple

Specifies the possible date formats as a list present in the date column

Example

dateCol
Mon 23 Mar 2020
17/02/2020
03/21/2020
04/24/2020 16_25

Function	Result
parse_date(dateCol, 'EEE dd MMM,yyyy', 'dd/MM/yyyy', 'MM/dd/yyyy', 'MM/dd/yyyy', MM/dd/yyyy HH_mm)	Mon 23 Mar,2020, Mon 17 Feb,2020, Sat 21 Mar,2020

Convert Timezone

convert_timezone(datetime,current_tz,convert_tz)

Returns the date and time value in converted timezone

Parameters

Name

Description

datetime

Datetime

Specifies the date-time column.

current_tz

Text

The current time zone value should be in 'hh:mm' format

convert_tz

Text

The difference in time between the current time zone and the alternate zone

Example

Data column
2004-01-01 12 00 00

Function	Result
convert_timezone(Data column,'+00:00','+10:00')	2004-01-01 22:00:00

Seconds To Time

sec_to_time(seconds)

Converts the given seconds into time and returns it in the format HH:mm:ss. If given number is negative, a minus sign will be added before the result.

Parameters

Name

Description

seconds

Number

Specifies the date time column or the exact number of seconds to be converted. The parameter can also be unix timestamp in milliseconds.

Example

Function	Result
sec_to_time('86399')	23:59:59

Remove Symbols

remove_symbols(string)

Removes all characters from a text that are not letters, numbers, accented Latin characters, non-Latin characters, or whitespace.

Parameters

Name

Description

string

Text

Parameter must be a Text column or a Text, or an expression that returns a Text.

Example

Function	Result
remove_symbols('Abcd$%```')	Abcd

Substring in-between

substring_between(fulltext,start_text,end_text,pos)

Returns the characters between two given texts from the main text.

Parameters

Name	Description
fulltext Text	All parameters must be of Text or a Text column, or an expression that returns a Text
start_text Text	Specifies the Text value from which the sub text should be obtained
end_text Text	Specifies the Text value to which the sub text should be obtained
pos Number	[Optional] It is an optional positive numeric value which specifies the position of the matching delimiter from which the sub text should be obtained

Example

Function	Result
substring_between('Sunday', 'n', 'y', 1)	da

Substring Index

substring_count(fulltext,sub_text)

Returns the count of occurrence of given sub text in the given text

Parameters

Name

Description

fulltext

Text

The parameter must be of Text or a Text column, or an expression that returns a Text

sub_text

Text

Specifies the sub text whose count of occurrences is to be obtained

Example

Function	Result
substring_count('Sunday', 'day')	1

Substring Index

substring_index(fulltext,delimiter,count)

Returns the sub text before the delimiter, based on the count of occurrences of the delimiter in the given text column

Parameters

Name

Description

fulltext

Text

All parameters must be of Text or a Text column, or an expression that returns a Text

delimiter

Text

Specifies a delimiter based on which the sub text is to be obtained from the given value

count

Number

It is a numeric value which specifies the number of delimiters before which the sub text should be obtained

Example

Function	Result
substring_index('how.are.you', '.' , 1)	how

Microsecond

microsecond(datetime)

Returns the microsecond value from the given date-time value

Parameters

Name

Description

datetime

Datetime

Specifies a date time column

Example

Data column
1990-08-07 10 35 23 3427

Function	Result
microsecond(Data column)	342700

Make Date

make_date(year,numOfDays)

Returns the date value by adding the number of days to the given year

Parameters

Name

Description

year

Number

Specifies the year which must be a number, a numeric column or an expression that returns a number

numOfDays

Number

Specifies the number of days which must be a number, a numeric column or an expression that returns a number

Example

Function	Result
make_date('2020', '30')	01/30/2020

Add Time

add_time(datetime,time)

Adds the specified time to the given date-time column.

Parameters

Name

Description

datetime

Datetime

Specifies a date-time column.

time

Text

Specifies the time to increase the date-time.

Example

Data column
12/08/2018 07 58 59

Function	Result
add_time(Data column, '01:00:00')	12/08/2018 08:58:59

Sub Time

sub_time(datetime,time)

Returns the datetime by subtracting the specified time from the given value with time value

Parameters

Name

Description

datetime

Datetime

Specifies a date-time column

time

Text

Specifies the time to decrement from the date-time value. Value can be in the format `HH:mm:ss`

Example

Data column
12/08/2018 07 58 59

Function	Result
sub_time(Data column, '01:00:00')	12/08/2018 06:58:59

Period Add

period_add(yearMonth,numOfMonths)

Adds the specified number of months to the first parameter and returns in the 'year-month' format

Parameters

Name

Description

yearMonth

Number

Parameter must be a Number column or a number, or an expression that returns a number

numOfMonths

Number

Parameter must be a Number column or a number, or an expression that returns a number

Example

Function	Result
period_add(20208,6)	20212

Period Difference

period_diff(yearMonth1,yearMonth2)

Returns the difference between two periods in months

Parameters

Name

Description

yearMonth1

Number

Parameter must be a Number column or a number, or an expression that returns a number

yearMonth2

Number

Parameter must be a Number column or a number, or an expression that returns a number

Example

Function	Result
period_diff('198608','198602')	6

To Currency

to_currency(col,currency)

Converts a numeric column to the currency format and datatype (value conversion between currencies is not supported yet)

Parameters

Name

Description

col

Decimal

Parameter must be a numeric column or an expression that returns a number

currency

Text

Parameter must be a Text value denoting the currency format

Example

sales
34.30
343.50
56.30

Function	Result
to_currency(sales, '$')	$34.30, $343.50, $56.30

To Length

to_length(Column,lengthUnit)

Converts a numeric column to the length datatype

Parameters

Name

Description

Column

Decimal

Parameter must be a numeric value from a column or an expression that returns a number

lengthUnit

Text

Parameter must be a Text value denoting the length unit that the given value needs to be converted into

Example

Column
50
20
30

Function	Result
to_length(Column, 'km')	50km, 20km, 30km

To Temperature

to_temperature(col,tempUnit)

Converts a numeric column to the temperature datatype

Parameters

Name

Description

col

Decimal

Parameter must be a numeric value from a column or an expression that returns a number

tempUnit

Text

Parameter must be a Text value denoting the temperature unit the given value is to be converted into

Example

col
234.3
343.5
56.3

Function	Result
to_temperature(col, 'K')	234.3K, 343.5K, 56.3K

Financial Year

financial_year(date,month)

Returns the financial year from the date specifying the fiscal start month.

Parameters

Name

Description

date

Datetime

Specifies a date column or a datetime column.

month

Number

Specifies the fiscal year starting month.

Example

Data column
09/25/2017

Function	Result
financial_year(Data column, '7')	2018

Quotient

quotient(num1,num2)

Returns the quotient of the given values in integer

Parameters

Name

Description

num1

Number

Parameter must be a Number column or a number, or an expression that returns a number

num2

Number

Parameter must be a Number column or a number, or an expression that returns a number

Example

Function	Result
quotient('12', '4')	3

Lists to Map

lists_to_map(param1,param2)

Returns a Map by combining two Lists.

Parameters

Name

Description

param1

List

Parameter must be of type List or a List column.

param2

List

Parameter must be of type List or a List column.

Example

list1
[20, 30]

list2
[A, B]

Function	Result
lists_to_map(list1, list2)	[20 : A , 30 : B]

Collect List

collect_list(param)

Collects all the values in the column as a list.

Parameters

Name

Description

param

Text

Parameter must be a column conforming to any one data type.

Example

Column
30
50
60

Function	Result
collect_list(Column)	[30, 50, 60], [30, 50, 60], [30, 50, 60]

Keys from Map

keys_from_map(param)

Returns the keys from the map passed as the parameter to the function.

Parameters

Name

Description

param

Map

Parameter must be a column containing maps.

Example

Map Column
key1; value1
key2; value2
key3; value3

Function	Result
keys_from_map(mapColumn)	key1, key2, key3

To Single Line

to_single_line(param)

Returns a text with multiple lines into a single line.

Parameters

Name

Description

param

Text

Parameter must be a Text, or a Text column, or an expression that returns a Text.

Example

Function	Result
to_single_line('Zoho\n\n\nOne\n\n')	Zoho One

Duration to Years

duration_to_years(duration)

Returns the number of years from the given duration value.

Parameters

Name

Description

duration

Duration

Specifies the Duration column.

Example

Data column
400.10:35:23

Function	Result
duration_to_years(Data column)	1

Duration to Months

duration_to_months(duration)

Returns the number of months from the given duration value.

Parameters

Name

Description

duration

Duration

Specifies the Duration column.

Example

Data column
800.10:35:23

Function	Result
duration_to_months(Data column)	26

Duration to Weeks

duration_to_weeks(duration)

Returns the number of weeks from the given duration value.

Parameters

Name

Description

duration

Duration

Specifies the Duration column.

Example

Data column
800.10:35:23

Function	Result
duration_to_weeks(Data column)	114

Duration to Days

duration_to_days(duration)

Returns the number of days from the given duration value.

Parameters

Name

Description

duration

Duration

Specifies the Duration column.

Example

Data column
400.10:35:23

Function	Result
duration_to_days(Data column)	400

Duration to Hours

duration_to_hours(duration)

Returns the number of hours from the given duration value.

Parameters

Name

Description

duration

Duration

Specifies the Duration column.

Example

Data column
800.10:35:23

Function	Result
duration_to_hours(Data column)	19120

Duration to Minutes

duration_to_minutes(duration)

Returns the number of minutes from the given duration value.

Parameters

Name

Description

duration

Duration

Specifies the Duration column.

Example

Data column
400.10:35:23

Function	Result
duration_to_minutes(Data column)	576635

Duration to Seconds

duration_to_seconds(duration)

Returns the number of seconds from the given duration value.

Parameters

Name

Description

duration

Duration

Specifies the Duration column.

Example

Data column
400.10:35:23

Function	Result
duration_to_seconds(Data column)	34598123

Add Years to Duration

add_years_to_duration(duration,num_of_years)

Returns the duration value by adding the given number of years.

Parameters

Name

Description

duration

Duration

Specifies the Duration column.

num_of_years

Number

The number of years to add to the duration value.

Example

Data column
1000 days 11:22:33

Function	Result
add_years_to_duration(Data column, 5)	2825 days 11:22:33

Add Months to Duration

add_months_to_duration(duration,num_of_months)

Returns the duration value by adding the given number of months.

Parameters

Name

Description

duration

Duration

Specifies the Duration column.

num_of_months

Number

The number of months to add to the duration value.

Example

Data column
1000 days 11:22:33

Function	Result
add_months_to_duration(Data column, 7)	1210 days 11:22:33

Add Weeks to Duration

add_weeks_to_duration(duration,num_of_weeks)

Returns the duration value by adding the given number of weeks.

Parameters

Name

Description

duration

Duration

Specifies the Duration column.

num_of_weeks

Number

The number of weeks to add to the duration value.

Example

Data column
1000 days 11:22:33

Function	Result
add_weeks_to_duration(Data column, 8)	1056 days 11:22:33

Add Days to Duration

add_days_to_duration(duration,num_of_days)

Returns the duration value by adding the given number of days.

Parameters

Name

Description

duration

Duration

Specifies the Duration column.

num_of_days

Number

The number of days to add to the duration value.

Example

Data column
1000 days 11:22:33

Function	Result
add_days_to_duration(Data column, 8)	1008 days 11:22:33

Add Hours to Duration

add_hours_to_duration(duration,num_of_hours)

Returns the duration value by adding the given number of hours.

Parameters

Name

Description

duration

Duration

Specifies the Duration column.

num_of_hours

Number

The number of hours to add to the duration value.

Example

Data column
1000 days 11:22:33

Function	Result
add_hours_to_duration(Data column, 5)	1000 days 16:22:33

Add Minutes of Duration

add_minutes_to_duration(duration,num_of_minutes)

Returns the duration value by adding the given number of minutes.

Parameters

Name

Description

duration

Duration

Specifies the Duration column.

num_of_minutes

Number

The number of minutes to add to the duration value.

Example

Data column
1000 days 11:22:33

Function	Result
add_minutes_to_duration(Data column, 5)	1000 days 11:27:33

Add Seconds to Duration

add_seconds_to_duration(duration,num_of_seconds)

Returns the duration value by adding the given number of seconds.

Parameters

Name

Description

duration

Duration

Specifies the Duration column.

num_of_seconds

Number

The number of seconds to add to the duration value.

Example

Data column
1000 days 11:22:33

Function	Result
add_seconds_to_duration(Data column, 5)	1000 days 11:22:38

Sub Years from Duration

sub_years_from_duration(duration,num_of_years)

Returns the duration value by subtracting the given number of years.

Parameters

Name

Description

duration

Duration

Specifies the Duration column.

num_of_years

Number

The number of years to subtract from the duration value.

Example

Data column
1000 days 11:22:33

Function	Result
sub_years_from_duration(Data column, 1)	635 days 11:22:33

Sub months from duration

Syntaxsub_months_from_duration(duration,num_of_months)

Returns the duration value by subtracting the given number of months.

Parameters

Name

Description

duration

Duration

Specifies the Duration column.

num_of_months

Number

The number of months to subtract from the duration value.

Example

Data column
1000 days 11:22:33

Function	Result
sub_months_to_duration(Data column, 3)	910 days 11:22:33

Sub Weeks from Duration

sub_weeks_from_duration(duration,num_of_weeks)

Returns the duration value by subtracting the given number of weeks.

Parameters

Name

Description

duration

Duration

Specifies the Duration column.

num_of_weeks

Number

The number of weeks to subtract from the duration value.

Example

Data column
1000 days 11:22:33

Function	Result
sub_weeks_from_duration(Data column, 10)	930 days 11:22:33

Sub Days from Duration

sub_days_from_duration(duration,num_of_days)

Returns the duration value by subtracting the given number of days.

Parameters

Name

Description

duration

Duration

Specifies the Duration column.

num_of_days

Number

The number of days to subtract from the duration value.

Example

Data column
1000 days 11:22:33

Function	Result
sub_days_from_duration(Data column, 30)	970 days 11:22:33

Sub Hours from Duration

sub_hours_from_duration(duration,num_of_hours)

Returns the duration value by subtracting the given number of hours.

Parameters

Name

Description

duration

Duration

Specifies the Duration column.

num_of_hours

Number

The number of hours to subtract from the duration value.

Example

Data column
1000 days 11:22:33

Function	Result
sub_hours_from_duration(Data column, 3)	1000 days 9:22:33

Sub Minutes from Duration

sub_minutes_from_duration(duration,num_of_minutes)

Returns the duration value by subtracting the given number of minutes.

Parameters

Name

Description

duration

Duration

Specifies the Duration column.

num_of_minutes

Number

The number of minutes to subtract from the duration value.

Example

Data column
1000 days 11:22:33

Function	Result
sub_minutes_from_duration(Data column, 10)	1000 days 11:12:33

Sub Seconds from Duration

sub_seconds_from_duration(duration,num_of_seconds)

Returns the duration value by subtracting the given number of seconds.

Parameters

Name

Description

duration

Duration

Specifies the Duration column.

num_of_seconds

Number

The number of seconds to subtract from the duration value.

Example

Data column
1000 days 11:22:33

Function	Result
sub_seconds_from_duration(Data column, 10)	1000 days 11:22:23

Timestamp Difference in Duration

timestamp_diff_in_duration(date1,date2)

Returns the difference of two timestamps value in duration.

Parameters

Name

Description

date1

Datetime

Specifies the Date, Time, or a Datetime column.

date2

Datetime

Specifies the Date, Time, or a Datetime column.

Example

Data column 1
17-08-1991 16:15:14

Data column 2
17-08-1990 14:15:16

Function	Result
timestamp_diff_in_duration('Data column 1', 'Data column 2')	365.1:59:58.0

Make Duration

make_duration(num_of_years,num_of_months,num_of_weeks,num_of_days,num_of_hours,num_of_minutes,num_of_seconds)

Returns the duration value using the given number of years, months, weeks, days, hours, minutes, and seconds. Enter 0 if no column or value is specified for any of the parameters.

Parameters

Name	Description
num_of_years Number	Specifies the number of years in the duration value.
num_of_months Number	Specifies the number of months in the duration value.
num_of_weeks Number	Specifies the number of weeks in the duration value.
num_of_days Number	Specifies the number of days in the duration value.
num_of_hours Number	Specifies the number of hours in the duration value.
num_of_minutes Number	Specifies the number of minutes in the duration value.
num_of_seconds Number	Specifies the number of seconds in the duration value.

Example

Function	Result
make_duration('12','11','23','11','9','10','5')	4882.9:10:5.0

Time to Minute

time_to_minute(time)

Returns the total number of minutes of the given time value.

Parameters

Name

Description

time

Time

Specifies a Time or a Datetime column.

Example

Data column
11:22:33

Function	Result
time_to_minute('Data column)	682

Make Time

make_time(num_of_hours,num_of_minutes,num_of_seconds)

Returns the time value using the given number of hours, minutes, and seconds.

Parameters

Name

Description

num_of_hours

Number

Specifies the number of hours in the time value.

num_of_minutes

Number

Specifies the number of minutes in the time value.

num_of_seconds

Number

Specifies the number of seconds in the time value.

Example

Function	Result
make_time(''11','23','25')	11:23:25

Is Current Hour

is_current_hour(time)

Returns true if the given time falls within the current hour, false otherwise.

Parameters

Name

Description

time

Time

Specifies a Time or a Datetime column.

Example

Data column
02:35:54

Function	Result
is_current_hour(Data column)	false

Is Last NHour

is_last_nhour(time,num_of_hours)

Returns true if the given hour falls under the last N number of hours from the current hour.

Parameters

Name

Description

time

Time

Specifies a Time, Duration or a Datetime column.

num_of_hours

Number

Specifies the number of hours.

Example

Data column
02:35:54

Function	Result
is_last_nhour(Data column, 3)	false

Is Next NHour

is_next_nhour(time,num_of_hours)

Returns true if the given hour falls under the next N number of hours from the current hour.

Parameters

Name

Description

time

Time

Specifies a Time, Duration or a Datetime column.

num_of_hours

Number

Specifies the number of hours.

Example

Data column
02:35:54

Function	Result
is_next_nhour(Data column, 3)	true

Start of Hour

start_of_hour(time)

Returns the starting hour of the given time value.

Parameters

Name

Description

time

Time

Specifies a Time, Duration or a Datetime column.

Example

Data column
10:35:23

Function	Result
start_of_hour(Data column)	10:00:00

End of Hour

end_of_hour(time)

Returns the ending hour of the given time value.

Parameters

Name

Description

time

Time

Specifies a Time, Duration or a Datetime column.

Example

Data column
10:35:23

Function	Result
end_of_hour(Data column)	11:00:00

Time Sum In Duration

time_sum_in_duration(time1,time2)

Returns the sum of two time values in the default duration format.

Parameters

Name

Description

time1

Time

Specifies a Time, Duration or a Datetime column.

time2

Time

Specifies a Time, Duration or a Datetime column.

Example

Data column 1
10:00:00

Data column 2
20:00:00

Function	Result
time_sum_in_duration('Data column 1', 'Data column 2')	1.6:0:0.0

Time Difference in Duration

time_diff_in_duration(time1,time2)

Returns the difference between two time values in the default duration format.

Parameters

Name

Description

time1

Time

Specifies a Time, Duration or a Datetime column.

time2

Time

Specifies a Time, Duration or a Datetime column.

Example

Data column 1
20:00:00

Data column 2
10:00:00

Function	Result
time_diff_in_duration('Data column 1', 'Data column 2')	0.10:0:0.0

Operators

You can use arithmetic and logical operators to combine two or more functions in a formula. You can also use these operators to customize the functions to create your formula column. The supported operators are given in the table below:

Operator	Description
!	Logical not
!=	Not equal to
%	Modulo
&	Bitwise AND
*	Multiply
+	Add
-	Subtract
/	Divide
<	Less than
<=	Less than
<=>	Equal to
<>	Not equal to
=	Equal to
==	Equal to
>	Greater than
>=	Greater than or equal to
^	Bitwise exclusive OR
AND	Logical AND
OR	Logical OR

Limitations

In the numeric functions, a maximum of 20 whole numbers and 15 decimal places are supported. When your function input exceeds either of these limits, an empty value is returned.

How to apply pivot transform?

How to filter data?

What are the dataset transforms available?

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
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
Holiday Forms
Form to PDF
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
Window functions to add formula columns
A window function is used to perform calculations across a group of rows on a table. This set of rows is termed a window. You can use window functions to perform summations and calculations based on a rolling window of data, relative to the current ...
Add formula
You can create new columns and customize them using the Formula column transform. Zoho DataPrep offers a variety of functions to suit your needs. Click here to know more about the functions. To create a formula column 1. Click on the Transform menu ...
Window functions to add formula columns
A window function is used to perform calculations across a group of rows on a dataset. A set of rows is termed a window. You can use window functions to perform summations and calculations based on a rolling window of data, relative to the current ...
Add prefix or suffix
DataPrep allows you to add prefixes and suffixes to your data. To add a prefix or a suffix to a column 1. Right-click on a column and select the Add Prefix or Suffix option from the context menu. 2. Enter the prefix, suffix, or both in the text ...
Create workspace and add new dataset
To import data, you need to create a new workspace, or open an existing workspace and add a dataset. To import data 1. Create a new workspace from the home page using the Create workspace option. Alternatively, you can select an existing workspace if ...

New to Zoho LandingPage?

Access Zoho LandingPage

Zoho LandingPage Resources

Wherever you are is as good as

your workplace

Workplace on mobile

Resources

Videos

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

Watch Now

eBooks

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

Download eBooks

Webinars

CRM Tips

Make the most of Zoho CRM with these useful tips.

Learn More

What are the functions available to add formula columns?

Functions available to add formula columns

Abs

Div

Add

Sub

Mul

Avg

Min

Max

Max Date

Min Date

Sum

Mode

Count

Count with Blanks

Variance

Standard Deviation

Binary

Cube Root

Ceiling

Character

Concat

Concat with Separator

Convert

Current Date

Current Timestamp

Date Format

Day of Month

Week Day

Day of the Week in name

Day of the Year

Decode

Degrees

Encode

Exponential

Floor

Greatest

Hexadecimal

Hour

If

Nested If

Else if

If Null

Initcap

Is Null

Is not Null

Not

Last Day

Least

Left

Length

In

Log

Log10

Log2

Lower

Itrim

Minute

Minute of the day

Minute of the Hour

Millisecond of the Day

Millisecond of Second

Is Leap Year

Mod

Month

Month as Name

Month and Year

Months in-between

Negative

Pi

Power

Quarter

Quarter Name

Quarter Year

Radians

Random

Regex Extract

Regex Replace

Replace