Demo using command line
Supported Operators
Operator | Description |
---|---|
+ | Additive operator / Unary plus / Concatenate string / Datetime addition Examples:
|
& | 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 statement | Description |
---|---|
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
Constant | Description |
---|---|
e | The value of e |
PI | The value of PI |
TRUE | The boolean true value |
FALSE | The boolean false value |
NULL | The null value |
Supported text functions
Function | Description |
---|---|
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 string/ text |
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
Function | Description |
---|---|
DATE(year,month,day) | Constructs a Date from Integer representations of the year, month (1=Jan), and day Example:
DATE(2021,8,24) |
DATEVALUE(date_string) | Constructs a Date from String representations of the year–month–day Example: DATE("2021-08-24") |
TIME(hour,min,second) | Constructs a Time instance from Integer representations of the hour, minute, 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 |