What calculation functions are supported

This article describes the calculation functions that are supported by our RPA BOT.

Note: These are the functions available to processes and bots. Wiki calculated fields use a separate, smaller set, and a function from this page will not parse in a wiki. If you are working in a wiki, read Calculated Fields in Wikis instead.

Using a function in a formula

To run a calculation, write a formula – a variable token that starts with =. Inside it you can combine functions, variables, numbers, text and operators. For the full variable and formula syntax see Working with variables in RPAs.

Operator precedence follows normal arithmetic rules: brackets are evaluated first, then multiplication and division, then addition and subtraction; the comparison operators (= != > < >= <=) are evaluated after the arithmetic. When in doubt, add brackets to make the order explicit and the formula easier to read.

Supported functions

The following DATA functions are supported:

• DROP: The DROP function is used to remove elements from a list or array, starting from the end.
• KEY: The KEY function returns the key at a given index (indexes are zero-based).
• MAP: The MAP function applies a function to each item in a collection and returns a new collection with the results.

The following DATE and TIME functions are supported:

• DATE: The DATE function is like a date constructor. It takes separate values for year, month, and day, and combines them into a single date.
• DATEDIF: The DATEDIF function is a useful tool for calculating the difference between two dates in various units, such as days, months, or years. It helps you determine the duration between two dates easily.
• DATESUB: The DATESUB function subtracts a specified number of days, months, or years from a given date.
• DAY: The DAY function extracts the day component from a given date.
• DAYS: Returns the number of days between two dates.
• EDATE: The EDATE function helps you find a date that is a certain number of months away from a starting date.
• EOMONTH: The EOMONTH function helps you find the last day of the month for a given date. This can be useful for determining due dates, deadlines, or maturity dates that always fall at the end of a month.
• EOQUARTER: The EOQUARTER function helps you find the last day of the quarter for a date that's a certain number of quarters away from a starting date.
• EOWEEK: The EOWEEK function helps you find the last day of the week for a given date.
• HOUR: Returns the hour of a date represented by a serial number. The hour is given as an integer, ranging from 0 to 23.
• MINUTE: Returns the minute of a date represented by a serial number. The minute is given as an integer, ranging from 0 to 59.
• MONTH: The MONTH function extracts the month component from a given date.
• NOW: The NOW function returns the current date and time.
• QUARTER: Returns the quarter of a date represented by a serial number. The quarter is given as an integer, ranging from 1 (January) to 4 (December).
• SECOND: Returns the seconds of a date represented by a serial number. The seconds is given as an integer, ranging from 0 to 59.
• TIME: Returns the serial number of a particular time.
• TODAY: The TODAY function returns the current date without the time component.
• WEEKDAY: The WEEKDAY function is used to determine the day of the week for a given date. It returns the day as a number, with different numbering systems based on the selected format. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.
• WEEKNUM: The WEEKNUM function is used to determine the week number of a specific date within a year. This function is particularly useful for organising and analysing data every week.
• WORKDAY: The WORKDAY function is used to calculate a date that is a specified number of working days before or after a given date, excluding weekends and optionally specified holidays.
• YEAR: The YEAR function extracts the year component from a given date.

The following LOGICAL functions are supported:

• AND: Use the AND function, one of the logical functions, to determine if all conditions in a test are TRUE.
• IF: An IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.
• NOT: The NOT function reverses the logic of its argument.
• OR: Use the OR function, one of the logical functions, to determine if any conditions in a test are TRUE.

The following ENGINEERING functions are supported:

• BIN2DEC: Converts a binary number to decimal.
• BIN2HEX: Converts a binary number to hexadecimal.
• BIN2OCT: Converts a binary number to octal.
• CONVERT: The CONVERT function is used to convert a number from one measurement system to another.
• DEC2BIN: The DEC2BIN function converts a decimal number to its binary equivalent.
• DEC2HEX: The DEC2HEX function converts a decimal number to its hexadecimal equivalent.
• DEC2OCT: The DEC2OCT function converts a decimal number to its octal equivalent.
• HEX2BIN: The HEX2BIN function converts a hexadecimal number to its binary equivalent.
• HEX2DEC: The HEX2DEC function converts a hexadecimal number to its decimal equivalent.
• HEX2OCT: The HEX2OCT function converts a hexadecimal number to its octal equivalent.
• NUMERIZE: The NUMERIZE function converts a written number i.e. "one hundred and twenty three" into a numeric value i.e. 123.
• OCT2BIN: Converts an octal number to binary.
• OCT2DEC: Converts an octal number to decimal.
• OCT2HEX: Converts an octal number to hexadecimal.

The following INFORMATION functions are supported:

• IFERROR: The IFERROR function returns a value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula.
• IFNA: The IFNA function returns the value you specify if the expression evaluates to the #N/A error; otherwise, it returns the result of the expression.
• IFNULL: The IFNULL function returns the first argument if it is not null, otherwise it returns the second argument.
• ISEVEN: The ISEVEN function checks whether a given number is even.
• ISNUMBER: The ISNUMBER function checks whether a value is a number.
• ISODD: The ISODD function checks whether a given number is odd.
• ISTEXT: The ISTEXT function checks whether a value is text.
• ISVALID: The ISVALID function looks at a piece of data and decides whether it fits certain rules or criteria.

The following MATHEMATICAL functions are supported:

• ABS: The ABS function returns the absolute value of a number, which is the number without its sign.
• AVERAGE: The AVERAGE function calculates the arithmetic mean of a set of numbers, which is the sum of the numbers divided by the count of numbers.
• CEILING: The CEILING function rounds a number up to the nearest integer or to the nearest multiple of significance.
• COS function: Returns the cosine of a given angle.
• COUNT: The COUNT function counts the number of elements in a comma-separated list.
• EXCHANGE: The EXCHANGE function is used to obtain the exchange rate between two currencies on a specific date.
• FLOOR: The FLOOR function rounds a number down to the nearest integer or to the nearest multiple of significance.
• INT: The INT function rounds a number down to the nearest integer.
• MAX: The MAX function returns the largest value from a set of numbers.
• MEDIAN: The MEDIAN function calculates the median of a set of numbers, which is the middle value when the numbers are arranged in order.
• MIN: The MIN function returns the smallest value from a set of numbers.
• MOD: The MOD function returns the remainder after a number (dividend) is divided by another number (divisor).
• MODE: The MODE function returns the most frequently occurring value in a set of numbers.
• PI: The PI function returns the mathematical constant π (pi), which is approximately 3.14159.
• POWER: The POWER function raises a number to a specified power.
• PRODUCT: The PRODUCT function multiplies all the numbers given as arguments and returns the product.
• RANDBETWEEN: The RANDBETWEEN function generates a random integer between two specified values, inclusive.
• ROUND: The ROUND function rounds a number to a specified number of digits.
• ROUNDDOWN: The ROUNDDOWN function rounds a number down towards zero to a specified number of digits.
• ROUNDUP: The ROUNDUP function rounds a number up, away from zero, to a specified number of digits.
• SIN: The SIN function returns the sine of a given angle in radians.
• SQRT: The SQRT function returns the square root of a number.
• SUM: The SUM function adds all the numbers given as arguments and returns the total sum.
• TAN: The TAN function returns the tangent of a given angle in radians.
• TOLERANCE: The TOLERANCE function is used to calculate the tolerance of a value based on a specified percentage.
• TRUNC: The TRUNC function truncates a number to an integer by removing the fractional part of the number.

The following TEXT functions are supported:

• CHAR: The CHAR function returns the character specified by a number code from the character set used by your computer.
• CLEAN: The CLEAN function removes all non-printable characters from a text string.
• CODE: The CODE function is used to find the numeric code of the first character in a text string.
• CONCAT: The CONCAT function combines multiple text strings into one continuous text string.
• DETECTLANGUAGE: The DETECTLANGUAGE function is used to identify the language of a given text.
• DIRNAME: The DIRNAME function extracts the parent directory from a given file path.
• FIND: The FIND function locates the position of a specific substring within a larger text string. It is case-sensitive and does not support wildcard characters.
• ID: The ID function return values associated with a South African 13-digit ID number.
• INITIALS: The INITIALS function returns the initials of a given name or text string.
• KEEPNUMBERS: The KEEPNUMBERS function returns a string with only the numeric characters from the input string.
• KEEPTEXT: The KEEPTEXT function returns a string with only the non-numeric characters from the input string.
• LEFT: The LEFT function extracts a specified number of characters from the beginning (left side) of a text string.
• LEN: The LEN function returns the number of characters in a text string, including spaces and special characters.
• LOWER: The LOWER function converts all uppercase letters in a text string to lowercase letters.
• LPAD: The LPAD function pads the left side of a text string with a specified character or characters until the string reaches a desired total length.
• MID: The MID function extracts a specific number of characters from a text string, starting at a specified position.
• PROPER: The PROPER function capitalizes the first letter of each word in a text string and converts all other letters to lowercase.
• REGEXEXTRACT: Extracts matching substrings according to a regular expression.
• REGEXREPLACE: Replaces substrings that match a regular expression with a specified replacement string.
• REGEXTEST: Tests whether a text string matches a regular expression.
• REPLACE: The REPLACE function replaces a specified portion of a text string with a different text string, based on the starting position and number of characters to replace.
• REPT: The REPT function repeats a text string a specified number of times.
• RIGHT: The RIGHT function extracts a specified number of characters from the end (right side) of a text string.
• RPAD: The RPAD function pads the right side of a text string with a specified character or characters until the string reaches a desired total length.
• SORT: The SORT function arranges the characters in a text string in a specified order, either ascending (A to Z) or descending (Z to A).
• SUBSTITUTE: The SUBSTITUTE function replaces occurrences of a specific substring within a text string with a different substring.
• TEXTAFTER: The TEXTAFTER function returns the text that occurs after a specified delimiter or occurrence in a text string.
• TEXTBEFORE: The TEXTBEFORE function returns the text that occurs before a specified delimiter or occurrence in a text string.
• TEXTSPLIT: The TEXTSPLIT function splits a text string into an array of substrings based on specified delimiters, and selects the fragment at the given index.
• TRANSLATE: The TRANSLATE function translates text to other languages.
• TRIM: The TRIM function removes extra spaces from a text string, leaving only single spaces between words and no leading or trailing spaces.
• UNIQUE: The UNIQUE function extracts unique values from a list or range of text strings, removing any duplicate entries.
• UPPER: The UPPER function converts all lowercase letters in a text string to uppercase letters.
• VALUE: The VALUE function converts a text string that represents a number into an actual numeric value.
• WORD: The WORD function is used to extract a specific number of words from text.