Contents[Hide]

Date and time functions

Function Description
datetime DATE(long year, long month, long day) Returns the sequential serial number that represents a particular date
datetime DATEVALUE(string date_text) Converts a date that is stored as text to a datetime value
long DAY(datetime date) Returns the day of a specified date, represented by datetime parameter. The day is given as an integer ranging from 1 to 31
long DAYS(datetime end_date, datetime start_date) Returns the number of days between two dates
long HOUR(datetime date) Returns the hour of the specified time value. The hour is given as a number, ranging from 0 (12:00 A.M.) till 23 (11:00 P.M.)
long MINUTE(datetime date) Returns the minutes of the specified time value. The minute is given as a number, ranging from 0 to 59
long MONTH(datetime date) Returns the month of a date represented by the specified serial number. The month is given as a number, ranging from 1 (January) to 12 (December)
datetime NOW() Returns the serial number of the current date and time
long SECOND(datetime date) Returns the seconds of the specified time value. The second is given as a number in the range 0 (zero) to 59
datetime TIME(long hour, long minute, long second) Returns the decimal number for the particular time
datetime TODAY() Returns the serial number of the current date
long WEEKDAY(datetime date) Returns the day of the week corresponding to the specified date. The day is given as a number, ranging from 1 (Sunday) to 7 (Saturday)
long YEAR(datetime date) Returns the year corresponding to the specified date. The year is returned as a number in the range 1900-9999

Engineering functions

Function Description
long BIN2DEC(string number)
New!
Converts a binary number to a decimal value
string DEC2BIN(long number, [long places])
New!
Converts a decimal number to a binary value
string DEC2HEX(long number, [long places])
New!
Converts a decimal number to a hexadecimal value
long DELTA(double number1, [double number2])
New!
Tests whether two values are equal to each other. Returns 1 if number1 is equal to number2; returns 0 in other case
long HEX2DEC(string number)
New!
Converts a hexadecimal number to a decimal value
long IMAGINARY(string inumber)
New!
Returns the imaginary coefficient of a complex number in x + yi or x + yj text format of expression
long IMREAL(string inumber)
New!
Returns the real coefficient of a complex number in x + yi or x + yj text format of expression
long OCT2DEC(string number)
New!
Converts an octal number to a decimal value

Financial functions

Function Description
double FV(double rate, long nper, double pmt, [double pv], [int type])
New!
Calculates the future value of an investment based on a constant interest rate. One can use FV with either a periodic, constant payments, or a single lump sum payment
double IRR(double value1, ..., double valueN, [double guess])
New!
Calculates the internal rate of return for a series of cash flows represented by the numbers in value1...valueN
double MIRR(double value1, ..., double valueN, double finance_rate, double reinvest_rate)
New!
Calculates the modified internal rate of a return for a series of periodic cash flows
double NPER(double rate, double pmt, double pv, [double fv], [long type])
New!
Calculates the number of periods for an investment based on periodic, constant payments and a constant interest rate
double NPV(double rate, double value1, ..., double valueN)
New!
Calculates the net present value of an investment by using a discount rate and a series of future payments and income
double PMT(double rate, long nper, double pv, [double fv], [int type])
New!
Calculates the payment for a loan based on constant payments and a constant interest rate
double PPMT(double rate, long per, long nper, double pv, [double fv], [int type])
New!
Calculates the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate
double PV(double rate, long nper, double pmt, [double fv], [int type])
New!
Calculates the present value of a loan or an investment, based on a constant interest rate
double RATE(long nper, double pmt, double pv, [double fv], [int type], [double guess])
New!
Calculates the interest rate per period of an annuity

Logical functions

Function Description
boolean AND(boolean a; boolean b; ...) Returns TRUE if all parameters are non-zero values
boolean FALSE() Returns the logical value FALSE
string IF(boolean aCondition, string ifTrue, string ifFalse) Returns ifTrue value if aCondition is non-zero value. Otherwise returns ifFalse value
boolean NOT(boolean a) Returns TRUE if a is zero or zero if a is non zero value
boolean OR(boolean a; boolean b; ...) Returns TRUE if any of the arguments is non-zero
string SWITCH(string expression, string value1, string result1, [string default or string value2, string result2],…[string default or string value3, string result3]) Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value is returned.
boolean TRUE() Returns the logical value TRUE
boolean XOR(boolean logical1, [boolean logical2],…) Returns a logical Exclusive Or of all arguments

Lookup and reference functions

Function Description
string HLOOKUP(string lookup_value, range lookup_range, long row) Searches for a value in the top row of a range, and then returns a value in the same column from a row one specifies in the range. Use HLOOKUP when your comparison values are located in a row across the top of a range, and you want to look down a specified number of rows.
string VLOOKUP(string lookup_value, range lookup_range, long column) Finds things in a range by row. For example, looks up a price of an automotive part by the part number.

Math and trigonometry functions

Function Description
double ABS(double a) Returns the absolute value of a double value
double ACOS(double a) Returns the arc cosine of a value; the returned angle is in the range 0.0 through pi
double ASIN(double a) Returns the arc sine of a value; the returned angle is in the range -pi/2 through pi/2
double ATAN(double a) Returns the arc tangent of a value; the returned angle is in the range -pi/2 through pi/2
double ATAN2(double y; double x) Returns the angle theta from the conversion of rectangular coordinates (x, y) to polar coordinates (r, theta)
double CBRT(double a) Returns the cube root of a double value
double CEIL(double a) Returns the smallest (closest to negative infinity) double value that is greater than or equal to the argument and is equal to a mathematical integer
double CEILING(double a) Returns the smallest (closest to negative infinity) double value that is greater than or equal to the argument and is equal to a mathematical integer
double COPYSIGN(double magnitude; double sign) Returns the first floating-point argument with the sign of the second floating-point argument
double COS(double a) Returns the trigonometric cosine of an angle
double COSH(double x) Returns the hyperbolic cosine of a double value
double EXP(double a) Returns Euler's number e raised to the power of a double value
double EXPM1(double x) Returns e^x -1
double FLOOR(double a) Returns the largest (closest to positive infinity) double value that is less than or equal to the argument and is equal to a mathematical integer
long GETEXPONENT(double d) Returns the unbiased exponent used in the representation of a double
double HYPOT(double x; double y) Returns sqrt(x^2 +y^2) without intermediate overflow or underflow
double IEEEREMAINDER(double f1; double f2) Computes the remainder operation on two arguments as prescribed by the IEEE 754 standard
double LOG(double a) Returns the natural logarithm (base e) of a double value
double LOG10(double a) Returns the base 10 logarithm of a double value
double LOG1P(double x) Returns the natural logarithm of the sum of the argument and 1
double NEXTAFTER(double start; double direction) Returns the floating-point number adjacent to the first argument in the direction of the second argument
double NEXTUP(double d) Returns the floating-point value adjacent to d in the direction of positive infinity
double POW(double a; double b) Returns the value of the first argument raised to the power of the second argument
double RANDOM() Returns a double value with a positive sign, greater than or equal to 0.0 and less than 1.0
double RINT(double a) Returns the double value that is closest in value to the argument and is equal to a mathematical integer
long ROUND(double a [, long num_digits]) Returns the closest long to the argument, with ties rounding up
double SCALB(double d; int scaleFactor) Return d ? 2^scaleFactor rounded as if performed by a single correctly rounded floating-point multiply to a member of the double value set
double SIGNUM(double d) Returns the signum function of the argument; zero if the argument is zero, 1.0 if the argument is greater than zero, -1.0 if the argument is less than zero
double SIN(double a) Returns the trigonometric sine of an angle
double SINH(double x) Returns the hyperbolic sine of a double value
double SQRT(double a) Returns the correctly rounded positive square root of a double value
double SUM(double a; double b; ...) Returns sum of all arguments. Read more...
double TAN(double a) Returns the trigonometric tangent of an angle
double TANH(double x) Returns the hyperbolic tangent of a double value
double TODEGREES(double angrad) Converts an angle measured in radians to an approximately equivalent angle measured in degrees
double TORADIANS(double angdeg) Converts an angle measured in degrees to an approximately equivalent angle measured in radians
double ULP(double d) Returns the size of an ulp of the argument

Statistical functions

Function Description
double AVERAGE(double a; double b; ...) Returns average of all arguments
double MAX(double a; double b; ...) Returns the greater of all arguments
double MIN(double a; double b, ...) Returns the smaller of all arguments

Text functions

Function Description
string CHAR(long number) Returns the character specified by a number
string CONCAT(string text1, [string text2],…) Combines the text from multiple strings
long FIND(string find_text, string within_text, [long start_num]) FIND locates one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string
string LEFT(string text, [long num_chars]) LEFT returns the specified number of characters in the specified text string
long LEN(string text) Returns the number of characters in a text string
string LOWER(string text) Converts all uppercase letters in a text string to lowercase
string MID(string text, long start_num, long num_chars) MID returns a specific number of characters from a text string, starting at the specified position
string REPLACE(string old_text, long start_num, long num_chars, string new_text) Replaces part of a text string, based on the number of characters you specify, with a different text string
string RIGHT(string text, [long num_chars]) RIGHT returns the specified number of last characters in the specified text string
string SUBSTITUTE(text, old_text, new_text, [instance_num]) Substitutes new_text for old_text in a text string
string TEXT(value, format_text) Converts a numeric value to text and lets one specify the display formatting by using special format strings. For example: TEXT("77.77", "$0.00") will result in "$77.77"
string UPPER(string text) Converts text to uppercase
double VALUE(string text) Converts a text string that represents a number to a number

Working with JIRA functions

Function Description
long JIRAISSUECOUNT(string jql_request, [string app_link_name])
New!
Returns the number of issues, which conform to specified JQL request. When appLinkName parameter value is not set, the primary JIRA application link is used. Otherwise, a specified application link will be taken. Example: "JIRAISSUECOUNT('Project = Test', 'Your Company JIRA')"
Note: If you need to add any other function or operand, feel free to contact our support team.