# Formula Reference V1

### Pre-19R1

See the tables below for details about available functions and operators, date formats, and number formats in the Vault CDMS formula and expression grammar.

## Functions and Operators

 Syntax Description Logical Operators < Less than <= Less than or equal to > Greater than >= Greater than or equal to numberEquals(number1, number2) Returns true if both numeric arguments (can include expressions) are equal. textEquals(text1, text2) Returns true if both text (string) arguments are equal. && AND || OR Arithmetic Operators * Multiplies two values. / Divides one value by another. % Remainder from one value divided by another. + Calculates the sum of two values. - Calculates the difference of two values. Math Functions min(number1, number2 ...) Returns the smallest number from the set. max(number1, number2, ...) Returns the largest number from the set. sqrt(number) Returns the square root. floor(number) Returns the next integer greater than the value (number). ceiling(number) Returns the next integer less than the value (number). round(number_of_digits) Rounds the value to the defined number of digits. value(text) Returns the string (text) as a number. Logical Functions and(expression1, expression2, ...) Returns true when both conditions are true. or(expression1, expression2, ....) Returns true when only one of the conditions is true. not(expression) Returns true when the condition is not true. if(expression1, value1, value2) Returns value 1 if the expression is true or value 2 if the expression is false. isNumber(text) Returns true when the value is a number. isNull(expression) Returns true when the value is null. picklistEquals(, Picklist..) Compares a picklist value with an existing global picklist label value and returns true or false. isBlank(expression) Returns true when the value is blank. Value Functions case(expression1, match1, result1, match2, result2, else_result) Compares the value of the expression with each matching value (match) and returns the paired result. If no values match, this function returns the last (else) argument. ifBlank(expression1, expression2) If the first argument (expression1) is blank, this function returns the second argument (expression2). If not blank, this function returns the first argument (expression1). ifNull(expression1, expression2) If the first argument (expression1) is null, this function returns the second argument (expression2). If not null, this function returns the first argument (expression1). Text Functions upper(value) Changes the text (value) to UPPERCASE. lower(value) Changes the text (value) to lowercase. left(value, pos) Returns the number of text characters specified (value) starting from a specified position (pos) on the left. right(value, pos) Returns the number of text characters specified (value) starting from a specified position (pos) on the right. middle(value, pos1, pos2) Returns the number of text characters between two specified positions (pos1 and pos2). length(value) Returns the character length. substitute(value, from, to) Substitutes a defined value. trim(value) Removes leading and trailing spaces. text(Date, format_mask) Applies the format (format_mask) to the specified Date. text(Number, format_mask) Applies the format (format_mask) to the specified number (can include an expression). Concatenate(text1, text2, ...) Use this function to connect text strings. Date & DateTime Functions date(year, month, day) Returns the date in the set date format. day(date) Returns the day from the date. month(date) Returns the month from the date. year(date) Returns the year from the date. today() Returns today's date in the vault’s time zone. now() Returns the current date and time (UTC). hour() Returns the current hour value in terms of 1 to 23 (UTC). minute() Returns the current minute value in the current hour in terms of 1 to 59 (UTC). second() Returns the current second value in the current minute in terms of 1 to 59 (UTC). days(number) Returns the specified number of days as an interval. months(number) Returns the specified number of months as an interval. years(number) Returns the specified number of years as an interval. dateDiff(end, start) Returns the difference between two dates in number of days. This function subtracts the start date (start) from the end date (end). (EDC) Note that dateDiff cannot process Unknown dates. If your study design allows Unknown values for a date-type Item, you must use substitute to replace the Unknown date with a date value. timeDiff(end, start)) Returns the difference between two times in number of minutes. This function subtracts the start time (start) from the end time (end). dateAdd(date, interval) Returns a date based on the offset (interval) from the starting date (date). You can use days, months, or years for the interval using the following syntax: days(#), months(#), or years(#). For example, you may use a formula field on an Audit object to calculate the Audit End Date. The end date is two weeks after the Audit Start Date. dateAdd(Object.audit_start_date    v, days(14)) dateTimeAdd (datetime,  interval) Returns a DateTime value based on the offset (interval) from the starting DateTime (datetime). You can use days, months, or years for the interval using the following syntax: days(#), months(#), or years(#). dateTimeDiff (end, start) Returns the difference between two DateTime values as a number of days, hours, and minutes. This function subtracts the start DateTime (start) from the end DateTime (end). dateInISO(date) Returns the Date (date) in ISO format. dateInISO(datetime) Returns the DateTime (datetime) as a date in ISO format. Vault Functions durationInValue Returns the total duration that a field had the specified value; does not support Date or Date/Time field types. If the field currently has the value, Vault uses the current date as the end date. If the field has never had the value, the function returns null/blank. Note: This function returns fractional days. previousValue Returns the value that the field had prior to the current value; does not support Date or Date/Time field types. firstTimeInValue Returns the date and time when a field was first set to the specified value; does not support Date or Date/Time field types. If the field has never had the value, the function returns null/blank. lastTimeInValue Returns the date and time when a field was last set to the specified value; doesn’t support Date or Date/Time field types. Note that even if a field has had a specific value several times, this returns the most recent time. If the field currently has the value, the function returns the current date and time. If the field has never had the value, the function returns null/blank. numTimesInValue Returns the number of times the field was set to the specified value; does not support Date or Date/Time field types.

## Date Formats

 Date Format Example Date Output d 1 (1-digit day of the month) dd 01 (2-digit day of the month) ddd Thu (3-letter day of the week) dddd Thursday (Full day of the week) mm 03 (2-digit month) mmm Mar (3-letter month) mmmm March (Full month) yy 17 (2-digit year) yyyy 2017 (Full year) dd-mm-yyyy 31-03-2017 yyyymmdd 20170331 dd.mmm.yyyy 30.03.2017 yyyy-mm-dd 2017-03-30 mmmm yyyy March 2017 dddd dd/mm/yy Thursday 31/03/17

## Number Formats

 Number Format Example Number Input Example Number Output 0 10.1 10 (Number as is, no decimal) # 10.10 10 (Number as is, no decimal, zero absent) 0.00 10.2531 10.25 (Number rounded to two decimal places) #.## 10.2501 10.25 (Number rounded to two decimal places, zero absent) \$# 100 \$100 (Number in currency form) #,### 1104 1,104 (Number with a comma 3 digits from the left. Multiple commas can be used) - 10 -10 (Adds the minus sign to the expression) % 9 %90 (Multiplies the number by 10 and displays it as a percentage) E 12345 1.234E4 (Separates the mantissa, which is the number of significant digits, and the exponent in scientific notation)