Demo using command line

Supported Operators

OperatorDescription
+Additive operator / Unary plus / Concatenate string / Datetime addition
Examples:
1+2 -> 3
"hello"+"word" -> "helloword"
TIME(10,10,0)+TIME(10,10,0) -> 20:20:00
DATETIME(2021,03,30,10,10,0)+(1/1440/60) -> 2021-03-30 10:10:01
DATE(2021,03,30)+TIME(22,10,36) -> 2021-03-30 22:10:36
&Concatenate string
Example:
"hello"&"word" -> "helloword"
Subtraction operator / Unary minus / Datetime subtraction
Examples:
1+2 -> 3
---+(-(++-2)) -> -2
TIME(10,10,0)-TIME(9,2,0) -> 01:08:00
DATETIME(2021,2,28,15,50,0)-DATETIME(2021,2,21,23,0,0) -> 6.70139 (days)
DATETIME(2021,2,28,15,50,0)-TIME(2,10,0) -> 2021-02-28 13:40:00
*Multiplication operator, can be omitted in front of an open bracket
/Division operator
%Remainder operator (Modulo)
^Power operator

Supported conditional statements

Conditional statementDescription
IF(logical_condition, value_if_true, value_if_false)Example:
IF(2>1,"Pass","Fail")
SWITCH(expression, val1,result1, [val2,result2], …, [default])Example:
SWITCH(3+2,5,"Apple",7,"Mango",3,"Good","N/A")

Supported logical and math functions

Function*Description
AND(logical1, [logical2], …)Determine if all conditions are TRUE
Example:
AND(2>1, 3>2) -> true
AND(2>1, 3<2) -> false
OR(logical1, [logical2], …)Determine if any conditions in a test are TRUE
Example:
OR(2>1, 3>2) -> true
OR(2>1, 3<2) -> true
NOT(logical)To confirm one value is not equal to another
Example:
NOT(2>1) -> false
NOT(false) -> true
XOR(logical1, [logical2], …)Exclusive OR function
SUM(number1, [number2],…)Return sum of numbers supplied
Example:
SUM(1,2,3,4,5) -> 15
AVERAGE(number1, [number2],…)Return average of numbers supplied
Example:
AVERAGE(1,2,3,4,5) -> 3
MIN(number1, [number2],…)Return the smallest value from the numbers supplied
Example:
MIN(1,2,3,4,5) -> 1
MAX(number1, [number2],…)Return the biggest value from the numbers supplied
Example:
MAX(1,2,3,4,5) -> 5
MOD(number, divisor)Get remainder of two given numbers after division operator.
Example:
MOD(23,6) -> 5
ROUND(number, num_digits)Returns the rounded approximation of given number using half-even rounding mode
( you can change to another rounding mode)
Example:
ROUND(20.085537,2) -> 20.09
ROUND(20126.08,-1) -> 20130
FLOOR(number, significance)Rounds a given number towards zero to the nearest multiple of a specified significance
Example:
FLOOR(3.7) -> 3
FLOOR(0.234,0.01) -> 0.23
CEILING(number, significance)Rounds a given number away from zero, to the nearest multiple of a given number
Example:
CEILING(2.1) -> 3
CEILING(-2.5, -2) -> -4
POWER(number, power)Returns the result of a number raised to a given power
Example:
POWER(2,8) -> 256
POWER(-2,2) -> 4
RAND()Produces a random number between 0 and 1
SIN(number)Returns the trigonometric sine of the angle given in radians
SINH(number)Returns the hyperbolic sine of a number
ASIN(number)Returns the arc sine of an angle, in the range of -pi/2 through pi/2
COS(number)Returns the trigonometric cos of the angle given in radians
Example:
COS(PI()) -> -1
COSH(number)Returns the hyperbolic cos of a number
ACOS(number)Returns the arc cosine of an angle, in the range of 0.0 through pi
TAN(number)Returns the tangent of the angle given in radians
TANH(number)Returns the hyperbolic tangent of a number
ATAN(number)Returns the arc tangent of an angle given in radians
ATAN2(x_number, y_number)Returns the arctangent from x- and y-coordinates
COT(number)Returns the cotangent of an angle given in radians.
Example:
COT(PI()/6) -> 1.73205
COTH(number)Returns the hyperbolic cotangent of a number
SQRT(number)Returns the correctly rounded positive square root of given number
LN(number)Returns the natural logarithm (base e) of given number
LOG10(number)Returns the logarithm (base 10) of given number
EXP(number)Returns e raised to the power of given number
ABS(number)Returns the absolute value of given number
FACT(number)Returns the factorial of a given number
SEC(number)Returns the secant of an angle given in radians
CSC(number)Returns the cosecant of an angle given in radians
PI()Return value of Pi
RADIANS(degrees)Convert degrees to radians
Example:
RADIANS(180.5) -> ~3.150319
DEGREES(radians)Convert radians to degrees
Example:
DEGREES(PI()) -> 180
INT(number)Returns the Integer value of given number

Supported Constants

ConstantDescription
eThe value of e
PIThe value of PI
TRUEThe boolean true value
FALSEThe boolean false value
NULLThe null value

Supported text functions

FunctionDescription
LEFT(text, num_chars)Extracts a given number of characters from the left side of a supplied text string
RIGHT(text, num_chars)Extracts a given number of characters from the right side of a supplied text string
MID(text, start_num, num_chars)Extracts a given number of characters from the middle of a supplied text string
REVERSE(text)Reverse a string
ISNUMBER(text)Check if a value is a number
LOWER(text)Converts all letters in the specified string to lowercase
UPPER(text)Converts all letters in the specified string to uppercase
PROPER(text)Capitalizes words given text string
Example: PROPER("capitalize the first letter") will return "Capitalize The First Letter"
TRIM(text)Removes extra spaces from text
LEN(text)Returns the length of a stringtext
TEXT(value, [format_text])Convert a numeric value into a text string. You can use the TEXT function to embed formatted numbers inside text
Example:
TEXT(123) -> 123
TEXT(DATEVALUE("2021-01-23"),"dd-MM-yyyy") -> 23-01-2021
TEXT(2.61,"hh:mm") -> 14:38
TEXT(2.61,"[hh]") -> 62
TEXT(2.61,"hh-mm-ss") -> 14-38-24
TEXT(DATEVALUE("2021-01-03")-DATEVALUE("2021-01-01"),"[h]") -> 48
TEXT(TIME(12,00,00)-TIME(10,30,10),"hh hours and mm minutes and ss seconds") -> "01 hours and 29 minutes and 50 seconds"
REPLACE(old_text, start_num, num_chars, new_text)Replaces characters specified by location in a given text string with another text string
Example:
REPLACE("ABC123",4,3,"456") -> ABC456
SUBSTITUTE(text, old_text, new_text)Replaces a set of characters with another
Example:
SUBSTITUTE("123-455-3321","-","") -> 1234553321
FIND(find_text, within_text, [start_num])Returns the location of a substring in a string (case sensitive)
Example:
FIND("a","ABCDabcABCabc") -> 5
FIND("ab","ABCDabcABCabc",6) -> 11
SEARCH(find_text, within_text, [start_num])Returns the location of a substring in a string (case insensitive)
Example:
SEARCH("A","ABC") -> 1
SEARCH("A","AEHABC",4) -> 4
CONCAT(text1, text2, text3,…)Combines the text from multiple strings
ISBLANK(text)Returns TRUE when a given string is null or empty, otherwise return FALSE
REPT(text, repeat_time)Repeats characters a given number of times
CHAR(char_code)Return character from ascii code
Example:
CHAR(97) -> a
CODE(char)Returns a ascii code of a character
Example:
CODE("a") -> 97

Supported date&time functions

FunctionDescription
DATE(year,month,day)Constructs a Date from Integer representations of the yearmonth (1=Jan), and day
Example:
DATE(2021,8,24)
DATEVALUE(date_string)Constructs a Date from String representations of the yearmonthday
Example:
DATE("2021-08-24")
TIME(hour,min,second)Constructs a Time instance from Integer representations of the hourminute, and second
SECOND(time_instance)
SECOND(datetime_instance)
Returns the second component of a Time, Datetime
Example:
SECOND(TIME(12,21,10)) -> 10
MINUTE(time_instance)
MINUTE(datetime_instance)
Returns the minute component of a Time, Datetime
Example:
MINUTE(TIME(12,21,10)) -> 21
HOUR(time_instance)
HOUR(datetime_instance)
Returns the hour component of a Time, Datetime
Example:
HOUR(TIME(12,21,10)) -> 12
DAY(date_instance)
DAY(datetime_instance)
Returns the day component of a Date, Datetime
Example:
DAY(DATE(2021,08,24)) -> 24
MONTH(date_instance)
MONTH(datetime_instance)
Returns the month component of a Date, Datetime
Example:
MONTH(DATE(2021,08,24)) -> 8
YEAR(date_instance)
YEAR(datetime_instance)
Returns the year component of a Date, Datetime
Example:
YEAR(DATE(2021,08,24)) -> 2021
NOW()Returns the current Datetime based on a GMT calendar
TODAY()Returns the current date in the current user’s time zone.
EDATE(start_date, months)Add month to Date, Datime
Example:
EDATE(DATEVALUE("2008-07-13"),2) -> 2008-09-13 00:00:00
WEEKDAY(date_instance)Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday)
Example:
WEEKDAY(DATEVALUE("2008-02-14")) -> 5
WEEKNUM(date_instance)Returns the week number of a specific date. For example, the week containing January 1 is the first week of the year, and is numbered week 1
Example:
WEEKNUM(DATEVALUE("2008-02-14")) -> 7
WORKDAY(start_date_instance, days)Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date).
Examples:
WORKDAY(DATEVALUE("2021-03-30"),61) -> 2021-06-23 00:00:00
WORKDAY(DATEVALUE("2021-03-30"),-3.01) -> 2021-03-24 00:00:00
NETWORKDAYS(start_date_instance, end_date_instance)Returns the number of whole workdays between two dates
Example:
NETWORKDAYS(DATEVALUE("2021-03-30"),DATEVALUE("2022-05-16")) -> 295
EOMONTH(start_date_instance, months)Calculates the last day of the month after adding a specified number of months to a date, datetime
Examples:
EOMONTH(DATE(2021,01,01),3) -> 2021-04-30
EOMONTH(DATETIME(2021,01,01,12,0,0),3) -> 2021-04-30
DATEDIF(start_date, end_date, unit)Calculates the number of days, months, or years between two dates.
Examples:
DATEDIF(DATEVALUE("2021-01-31"),DATEVALUE("2029-08-29"),"Y") -> 8
DATEDIF(DATEVALUE("2021-01-31"),DATEVALUE("2029-08-29"),"M") -> 102
DATEDIF(DATEVALUE("2021-01-31"),DATEVALUE("2029-08-29"),"D") -> 3132
DATEDIF(DATEVALUE("2021-01-31"),DATEVALUE("2029-08-29"),"YM") -> 6
DATEDIF(DATEVALUE("2021-01-31"),DATEVALUE("2029-08-29"),"MD") -> 29
DAYS(end_date, start_date)Returns the number of days between two dates
Example:
DAYS(DATEVALUE("2021-01-05"),DATEVALUE("2021-01-03")) -> 2