Vom Formulas
Vom is a low-code tool that uses a language inspired by Excel formulas. For example, you could use this formula in a conditional block to check if a person is underage:
DATEDIF(NOW(), birth_date, "Y") < 18
Most Excel formula functions are supported in Vom Formulas. While Excel uses cell references like A1, in Vom you use descriptive variable names. These variables can come from the policy input or from assignments made in previous blocks.
Date and Time Handling
Date and time input (AKA datetime) variables or feature values should be
provided in the API or batch decision CSV as
combined date and time ISO 8601
strings in the UTC time zone (e.g. 2024-06-12T10:15:00Z). On the other hand,
when executing formulas datetimes are represented as numbers, in the same way as
Excel. That is, a datetime variable is a floating point number representing the
number of days since 1900-01-01. The fractional part of the number is the
fraction of the day that has passed since midnight. See
the Excel docs
for more information. Usually there's no need to notice this, since the date
functions handle these numbers transparently, and the Vom formula system will
prevent any errors from mixing variables of different types.
Document Handling
Document variables hold files such as images, PDFs, or scanned forms. Since
formulas are designed for computing with simple values, document variables are
converted to booleans when used in a formula: TRUE if the document was
provided, FALSE otherwise. This allows you to easily check whether an optional
document was supplied. For example:
IF(proof_of_income, "Document provided", "Missing document")
The actual document content is not accessible through formulas. Instead, documents are consumed by Generative AI blocks, where they can be attached to prompts for tasks like information extraction or verification.
Supported Functions
Vom supports the following Excel-compatible functions, organized alphabetically.
ABS
Returns the absolute value of a number.
Signature: ABS(number)
Parameters:
number: The number whose absolute value you want.
Example:
ABS(-5)
Returns 5.
AND
Returns TRUE if all arguments are TRUE.
Signature: AND(logical1, [logical2], ...)
Parameters:
logical1, logical2, ...: One or more boolean values or expressions.
Example:
AND(age >= 18, income > 50000)
Returns TRUE only if both conditions are met.
AVERAGE
Returns the average (arithmetic mean) of the provided numbers.
Signature: AVERAGE(number1, [number2], ...)
Parameters:
number1, number2, ...: One or more numbers or arrays of numbers to average.
Example:
AVERAGE(10, 20, 30)
Returns 20.
CEILING
Rounds a number up to the nearest multiple of significance.
Signature: CEILING(number, [significance])
Parameters:
number: The number to round up.significance(optional): The multiple to round up to. Defaults to1.
Example:
CEILING(4.3, 1)
Returns 5.
CEILING(4.3, 0.5)
Returns 4.5.
CHOOSE
Returns a value from a list based on an index.
Signature: CHOOSE(index, value1, [value2], ...)
Parameters:
index: The 1-based position of the value to return.value1, value2, ...: The values to choose from. All values must be the same type.
Example:
CHOOSE(2, "Red", "Green", "Blue")
Returns "Green".
COUNTA
Counts the number of values in the provided arguments.
Signature: COUNTA(value1, [value2], ...)
Parameters:
value1, value2, ...: Values or arrays to count.
Example:
COUNTA(items)
Returns the number of items in the array.
DATE
Creates a datetime value from year, month, and day components.
Signature: DATE(year, month, day)
Parameters:
year: The year (0-9999). Years 0-1899 are interpreted as 1900-3799.month: The month (1-12). Values outside this range adjust the year accordingly.day: The day of the month. Values outside the valid range adjust the month accordingly.
Example:
DATE(2024, 6, 15)
Returns the datetime value for June 15, 2024.
DATEDIF
Calculates the difference between two dates in years, months, or days.
Signature: DATEDIF(start_date, end_date, [unit])
Parameters:
start_date: The start date.end_date: The end date.unit(optional): The unit of time to return:"Y": Complete years"M": Complete months"D": Days (default)
Example:
DATEDIF(birth_date, NOW(), "Y")
Returns the person's age in complete years.
Unlike Excel, Vom allows negative date differences (when end_date is before start_date).
DAY
Extracts the day of the month from a datetime value.
Signature: DAY(datetime)
Parameters:
datetime: The datetime value.
Example:
DAY(hire_date)
Returns the day component (1-31) of the hire date.
DAYS
Returns the number of days between two dates.
Signature: DAYS(start_date, end_date)
Parameters:
start_date: The first date.end_date: The second date.
Example:
DAYS(start_date, end_date)
Returns the absolute number of days between the two dates.
EDATE
Returns a date that is a specified number of months before or after a given date.
Signature: EDATE(start_date, months)
Parameters:
start_date: The starting date.months: The number of months to add (positive) or subtract (negative).
Example:
EDATE(contract_date, 6)
Returns the date 6 months after the contract date.
EOMONTH
Returns the last day of the month a specified number of months before or after a given date.
Signature: EOMONTH(start_date, months)
Parameters:
start_date: The starting date.months: The number of months to add (positive) or subtract (negative).
Example:
EOMONTH(invoice_date, 0)
Returns the last day of the month containing the invoice date.
EXP
Returns e raised to the power of a given number. The constant e is approximately 2.71828.
Signature: EXP(number)
Parameters:
number: The exponent to raise e to.
Example:
EXP(1)
Returns approximately 2.71828.
FILTER
Filters an array based on a condition specified by a lambda function.
Signature: FILTER(array, LAMBDA(element, condition))
Parameters:
array: The array to filter.LAMBDA: A function that takes each element and returns TRUE to include it.
Example:
FILTER(transactions, LAMBDA(t, t > 100))
Returns all transactions greater than 100.
FIND
Returns the position of a substring within a text string.
Signature: FIND(find_text, within_text, [start_num])
Parameters:
find_text: The text to find.within_text: The text to search within.start_num(optional): The position to start searching from. Defaults to1.
Example:
FIND("@", email)
Returns the position of "@" in the email address. Returns -1 if not found.
FLOOR
Rounds a number down to the nearest multiple of significance.
Signature: FLOOR(number, [significance])
Parameters:
number: The number to round down.significance(optional): The multiple to round down to. Defaults to1. Cannot be zero.
Example:
FLOOR(4.7, 1)
Returns 4.
FLOOR(4.7, 0.5)
Returns 4.5.
FV
Calculates the future value of an investment based on periodic, constant payments and a constant interest rate.
Signature: FV(rate, nper, pmt)
Parameters:
rate: The interest rate per period.nper: The total number of payment periods.pmt: The payment made each period (negative for outgoing payments).
Example:
FV(0.05, 10, -100)
Returns 1257.79 (the future value of investing $100 per period for 10 periods
at 5% interest).
HOUR
Extracts the hour component from a datetime value.
Signature: HOUR(datetime)
Parameters:
datetime: The datetime value.
Example:
HOUR(transaction_time)
Returns the hour (0-23) of the transaction.
IF
Returns one value if a condition is TRUE and another value if FALSE.
Signature: IF(condition, value_if_true, value_if_false)
Parameters:
condition: A boolean expression to evaluate.value_if_true: The value to return if condition is TRUE.value_if_false: The value to return if condition is FALSE.
Example:
IF(score >= 70, "Pass", "Fail")
Returns "Pass" if score is 70 or higher, otherwise "Fail".
Both value_if_true and value_if_false must have the same type.
IFERROR
Returns a specified value if an expression results in an error; otherwise returns the expression result.
Signature: IFERROR(value, value_if_error)
Parameters:
value: The expression to evaluate.value_if_error: The value to return ifvalueresults in an error.
Example:
IFERROR(amount / quantity, 0)
Returns 0 if the division results in an error (e.g., division by zero),
otherwise returns the division result.
IRR
Calculates the internal rate of return for a series of cash flows.
Signature: IRR(values)
Parameters:
values: An array of numbers representing cash flows. Must contain at least one positive and one negative value.
Example:
IRR([-1000, 300, 300, 300, 300, 300])
Returns approximately 0.1524 (15.24% rate of return).
LEFT
Returns the specified number of characters from the beginning of a text string.
Signature: LEFT(text, [num_chars])
Parameters:
text: The text string.num_chars(optional): The number of characters to extract. Defaults to1.
Example:
LEFT("Hello", 2)
Returns "He".
LOWER
Converts text to lowercase.
Signature: LOWER(text)
Parameters:
text: The text to convert.
Example:
LOWER("Hello World")
Returns "hello world".
MAP
Transforms each element in an array using a lambda function.
Signature: MAP(array, LAMBDA(element, transformation))
Parameters:
array: The array to transform.LAMBDA: A function that takes each element and returns the transformed value.
Example:
MAP(prices, LAMBDA(p, p * 1.1))
Returns all prices increased by 10%.
When using multiple arrays, they must all have the same length:
MAP(quantities, prices, LAMBDA(q, p, q * p))
MAX
Returns the largest value from the provided numbers.
Signature: MAX(number1, [number2], ...)
Parameters:
number1, number2, ...: One or more numbers or arrays of numbers.
Example:
MAX(5, 10, 3)
Returns 10.
MEDIAN
Returns the median (middle value) of the provided numbers.
Signature: MEDIAN(number1, [number2], ...)
Parameters:
number1, number2, ...: One or more numbers or arrays of numbers.
Example:
MEDIAN(1, 5, 3, 9, 2)
Returns 3.
MIN
Returns the smallest value from the provided numbers.
Signature: MIN(number1, [number2], ...)
Parameters:
number1, number2, ...: One or more numbers or arrays of numbers.
Example:
MIN(5, 10, 3)
Returns 3.
MINUTE
Extracts the minute component from a datetime value.
Signature: MINUTE(datetime)
Parameters:
datetime: The datetime value.
Example:
MINUTE(transaction_time)
Returns the minute (0-59) of the transaction.
MOD
Returns the remainder after dividing a number by a divisor.
Signature: MOD(number, divisor)
Parameters:
number: The dividend.divisor: The divisor.
Example:
MOD(10, 3)
Returns 1.
MODE
Returns the most frequently occurring value in a set of numbers.
Signature: MODE(number1, [number2], ...)
Parameters:
number1, number2, ...: One or more numbers or arrays of numbers.
Example:
MODE(1, 2, 2, 3, 3, 3)
Returns 3.
MONTH
Extracts the month from a datetime value.
Signature: MONTH(datetime)
Parameters:
datetime: The datetime value.
Example:
MONTH(order_date)
Returns the month (1-12) of the order date.
NETWORKDAYS
Returns the number of working days (excluding weekends) between two dates.
Signature: NETWORKDAYS(start_date, end_date)
Parameters:
start_date: The start date.end_date: The end date.
Example:
NETWORKDAYS(project_start, project_end)
Returns the number of business days in the project timeline.
NOT
Returns the opposite of a boolean value.
Signature: NOT(logical)
Parameters:
logical: A boolean value or expression.
Example:
NOT(is_blocked)
Returns TRUE if is_blocked is FALSE, and vice versa.
NOW
Returns the current date and time.
Signature: NOW()
Example:
NOW()
Returns the current datetime.
NPER
Calculates the number of periods for an investment based on periodic, constant payments and a constant interest rate.
Signature: NPER(rate, pmt, pv)
Parameters:
rate: The interest rate per period.pmt: The payment made each period.pv: The present value (the initial investment).
Example:
NPER(0.05, -100, 1000)
Returns approximately 14.21 periods.
NPV
Calculates the net present value of an investment based on a discount rate and a series of future cash flows.
Signature: NPV(rate, value1, [value2], ...)
Parameters:
rate: The discount rate per period.value1, value2, ...: Future cash flows (the first cash flow is assumed to occur at the end of period 1).
Example:
NPV(0.1, 100, 200, 300)
Returns 481.59.
NPV assumes cash flows occur at the end of each period. If you have an initial investment at time 0, add it separately after the NPV calculation.
OR
Returns TRUE if any argument is TRUE.
Signature: OR(logical1, [logical2], ...)
Parameters:
logical1, logical2, ...: One or more boolean values or expressions.
Example:
OR(status = "approved", status = "pending")
Returns TRUE if status is either "approved" or "pending".
PMT
Calculates the periodic payment for a loan based on constant payments and a constant interest rate.
Signature: PMT(rate, nper, pv)
Parameters:
rate: The interest rate per period.nper: The total number of payment periods.pv: The present value (loan amount).
Example:
PMT(0.05, 12, 10000)
Returns -1128.25 (the monthly payment for a $10,000 loan at 5% for 12
periods).
POWER
Returns the result of a number raised to a power.
Signature: POWER(base, exponent)
Parameters:
base: The base number.exponent: The exponent to raise the base to.
Example:
POWER(2, 3)
Returns 8.
PV
Calculates the present value of an investment based on periodic, constant payments and a constant interest rate.
Signature: PV(rate, nper, pmt)
Parameters:
rate: The interest rate per period.nper: The total number of payment periods.pmt: The payment made each period.
Example:
PV(0.05, 10, -100)
Returns 772.17 (the present value of receiving $100 per period for 10 periods
at 5% interest).
RAND
Returns a random number between 0 (inclusive) and 1 (exclusive).
Signature: RAND()
Example:
RAND()
Returns a value like 0.54321 (varies each time).
RATE
Calculates the interest rate per period for a loan or investment.
Signature: RATE(nper, pmt, pv)
Parameters:
nper: The total number of payment periods.pmt: The payment made each period.pv: The present value.
Example:
RATE(12, -100, 1000)
Returns approximately 0.0293 (2.93% per period).
REDUCE
Reduces an array to a single value by applying a lambda function cumulatively.
Signature: REDUCE(initial_value, array, LAMBDA(accumulator, element, result))
Parameters:
initial_value: The starting value for the accumulator.array: The array to reduce.LAMBDA: A function that takes the accumulator and current element, and returns the new accumulator value.
Example:
REDUCE(0, amounts, LAMBDA(total, amount, total + amount))
Returns the sum of all amounts in the array.
RIGHT
Returns the specified number of characters from the end of a text string.
Signature: RIGHT(text, [num_chars])
Parameters:
text: The text string.num_chars(optional): The number of characters to extract. Defaults to1.
Example:
RIGHT("Hello", 2)
Returns "lo".
ROUND
Rounds a number to a specified number of decimal places.
Signature: ROUND(number, [num_digits])
Parameters:
number: The number to round.num_digits(optional): The number of decimal places. Defaults to0. Negative values round to the left of the decimal point.
Example:
ROUND(3.14159, 2)
Returns 3.14.
ROUND(1234, -2)
Returns 1200.
SECOND
Extracts the second component from a datetime value.
Signature: SECOND(datetime)
Parameters:
datetime: The datetime value.
Example:
SECOND(timestamp)
Returns the second (0-59) of the timestamp.
SUBSTITUTE
Replaces occurrences of old text with new text in a string.
Signature: SUBSTITUTE(text, old_text, new_text)
Parameters:
text: The original text.old_text: The text to replace.new_text: The replacement text.
Example:
SUBSTITUTE("Hello World", "World", "Vom")
Returns "Hello Vom".
TEXT
Formats a number as text with a specified format.
Signature: TEXT(value, format_text)
Parameters:
value: The number to format.format_text: A format string using zeros and an optional decimal point.
Example:
TEXT(42, "0000")
Returns "0042".
TEXT(3.14159, "0.00")
Returns "3.14".
TIME
Creates a time value (as a fraction of a day) from hour, minute, and second components.
Signature: TIME(hour, minute, second)
Parameters:
hour: The hour (0-23).minute: The minute (0-59).second: The second (0-59).
Example:
TIME(14, 30, 0)
Returns the time value for 2:30 PM (as a decimal fraction of a day).
TODAY
Returns the current date (without time).
Signature: TODAY()
Example:
TODAY()
Returns today's date at midnight.
UPPER
Converts text to uppercase.
Signature: UPPER(text)
Parameters:
text: The text to convert.
Example:
UPPER("Hello World")
Returns "HELLO WORLD".
VALUE
Converts a text representation of a number to a number.
Signature: VALUE(text)
Parameters:
text: A text string representing a number.
Example:
VALUE("123.45")
Returns 123.45.
WEEKDAY
Returns the day of the week for a given date.
Signature: WEEKDAY(datetime)
Parameters:
datetime: The datetime value.
Example:
WEEKDAY(appointment_date)
Returns the day of week (0=Monday, 6=Sunday).
WORKDAY
Returns a date that is a specified number of working days before or after a given date.
Signature: WORKDAY(start_date, days)
Parameters:
start_date: The starting date.days: The number of working days to add (positive) or subtract (negative).
Example:
WORKDAY(order_date, 5)
Returns the date 5 working days after the order date.
XIRR
Calculates the internal rate of return for a schedule of cash flows that is not necessarily periodic.
Signature: XIRR(values, dates, [guess])
Parameters:
values: An array of cash flows. Must contain at least one positive and one negative value.dates: An array of dates corresponding to each cash flow.guess(optional): An initial guess for the rate. Defaults to0.1.
Example:
XIRR([-1000, 300, 400, 500], [start_date, date1, date2, date3])
Returns the annualized internal rate of return based on the actual dates of cash flows.
XNPV
Calculates the net present value for a schedule of cash flows that is not necessarily periodic.
Signature: XNPV(rate, values, dates)
Parameters:
rate: The discount rate to apply.values: An array of cash flows.dates: An array of dates corresponding to each cash flow.
Example:
XNPV(0.1, [-1000, 300, 400, 500], [start_date, date1, date2, date3])
Returns the net present value discounted based on the actual dates of cash flows.
YEAR
Extracts the year from a datetime value.
Signature: YEAR(datetime)
Parameters:
datetime: The datetime value.
Example:
YEAR(registration_date)
Returns the year of the registration date.