Formulas in Laserfiche Forms

Laserfiche Forms supports a variety of functions that you can use in the following fields in a form: single line, number, date, currency, table, and collection. After you enter a formula into a form, the field on the form is populated automatically with the result of the formula calculation.

To apply a formula to a field, go to the Advanced tab of the field and either type in the text box within the Calculation section or use the Insert formula button in the top left.

When you type in the text box, be sure to use the OpenFormula standard specified in the Formulas section below. The OpenFormula standard is the same standard used by Microsoft Excel and Google Sheets. Also, add an equal sign "=" to the beginning of the formula. For example, type "=SUM(collection1.val1, 2)".

Note: A read-only field configured to use a formula cannot be used to trigger a lookup rule.

Formulas
Name Format Description
ABS ABS(value)

The ABS function returns the absolute value of the specified value.

"=ABS(3)" returns "3"

"=ABS(-3)" returns "3"

"=ABS(a)" returns nothing
ADD ADD(value1, value2)

The ADD function returns the sum of two values.

"=ADD(3, 3)" returns "6"
ADD_MONTHS ADD_MONTHS(date, months)

The ADD_MONTHS function adds months to a date object and returns a date object.

"=ADD_MONTHS(DATE(2015, 8, 11), 4)" returns "42349"
AND AND(value1, value2, ...)

The AND function returns "true" if all specified values are true; otherwise, it returns false.

"=AND(true, false)" returns "false"
AVERAGE AVERAGE(value1, value2, ...)

The AVERAGE function returns the average of a list of values.

"=AVERAGE(3, 4, 5, 6)" returns "4.5"
AVERAGEA AVERAGEA(value1, value2, ...)

The AVERAGEA function returns the average of a list of values, where "true" is treated as 1 and "false" and text are treated as 0.

"=AVERAGEA(true, false, "test", 4)" returns "1.25"
AVERAGEIF AVERAGEIF([value1, value2, ...], criterion)

The AVERAGEIF function returns the average of a list of number values that satisfy the criterion.

"=AVERAGEIF([1, 2, 3], ">2")" returns "3"
AVG AVG(value1, value2, ...)

The AVG function returns the average of a list of values.

"=AVG(3, 4, 5, 6)" returns "4.5"
CEILING CEILING(value, factor)

The CEILING function rounds the specified value up to the nearest multiple of the factor.

"=CEILING(10.1, 2)" returns "12"
CHAR CHAR(value)

The CHAR function returns a character representation of a number based on the Unicode table.

"=CHAR(76)" returns "L"
CHOOSE CHOOSE(index, val1, val2, ...)

The CHOOSE function returns the value at the index provided as the first argument.

"=CHOOSE(3, 10, 11, 12)" returns "12" because the third value is "12"
CHOOSEA CHOOSEA(index, val1, val2, ...)

The CHOOSEA function returns the value at the index in the first argument. If more than one input exists in one value, the function is flattened.

"=CHOOSEA(2, 1, [2,3], 4)" returns "2" because the argument becomes "1, 2, 3, 4"
CODE CODE(value)

The CODE function returns a number based on the Unicode table that represents the character.

"=CODE("L")" returns "76"
COLUMN COLUMN()

The COLUMN function returns the column number of a cell in a table. The first cell has a value of 1.

"=COLUMN()" returns "1" for a cell in column 1

"=COLUMN()=0" returns "TRUE" if the field is outside the table/collection
CONCATENATE CONCATENATE(value1, value2, ...)

The CONCATENATE function joins multiple strings together into one string. Also see JOIN.

"=CONCATENATE("a", "b", "c")" returns "abc"

"=CONCATENATE(CHAR(76), "aserfiche")" returns "Laserfiche"
COUNT COUNT(value1, value2, ...)

The COUNT function tallies the number of non-numeric, listed values.

"=COUNT(3, 2, 7, 4, 6)" returns "5"
COUNTIF COUNTIF([value1, value2, ...], "condition")

The COUNTIF function tallies the number of listed values that satisfy the condition.

"=COUNTIF([1, 4, 6], ">1")" returns "2"
DATE DATE(year, month, day)

The DATE function returns a date object representation of a date. You can use this date object in various other functions, including DAY, MONTH, and YEAR.

"=DATE(1985, 12, 2)" returns "31383"

Note: When used with a date-based variable or field, the date will be formatted as defined by the field or variable.
DATEDIF DATEDIF(start_date, end_date, time_unit)

The DATEDIF function calculates the difference between two date objects based on a specific unit.

"=DATEDIF(DATE(2017, 1, 2), DATE(2017, 1, 14), "M")" returns "0"

"=DATEDIF(DATE(2017, 1, 2), DATE(2017, 1, 14), "D")" returns "12"
DATEVALUE DATEVALUE(dateString)

The DATEVALUE function converts a date (stored as text) into a date object.

"=DATEVALUE("2017-1-14")" returns "42749"
DAY DAY(dateValue)

The DAY function returns the day of a given date.

"=DAY(TODAY())" returns "2" on January 2nd

"=DAY(DATE(1985, 12, 2))" returns "2"
DAYS360 DAYS360(start_date, end_date)

The DAYS360 function returns the number of days between two given dates. This is sometimes used in financial interest calculations.

"=DAYS360(DATE(2017, 1, 2), DATE(2018, 1, 3))" returns "361" because this is one day after a full year
DIV DIV(value1, value2)

The DIV function divides the first value by the second value.

"=DIV(2,4)" returns "0.5"

"=DIV(SUB(4,2), 2)" returns "1"

"=DIV(SUM(2, 4), COUNT(2, 4))" returns 3 (the average of "2" and "4').
EDATE EDATE(DateObject, months)

The EDATE function adds an integer number of months to a date object to return another date object. If the months are non-integer values, then the function is rounded and then added.

"=EDATE(DATE(2017,1,2), 1)" returns "42768"

Note: When used with a date-based variable or field, the date will be formatted as defined by the field or variable.
EOMONTH EOMONTH(DateObject, months)

The EOMONTH function adds an integer number of months to a date object then returns a date object with the day as the end of that month.

"=EOMONTH(DATE(2017,1,2), 1)" returns "42794"

"=EOMONTH(DATE(2017,1,2), -1)" returns "42735"

Note: When used with a date-based variable or field, the date will be formatted as defined by the field or variable.
EQ EQ(value1, value2)

The EQ function returns "true" if the two values are equal, "false" otherwise.

"=EQ(2,3)" returns "FALSE"
EVEN EVEN(value)

The EVEN function rounds the value up to the nearest even number.

"=EVEN(1)" returns "2"

"=EVEN(-1)" returns "-2"
EXACT EXACT(value1, value2)

The EXACT function converts both values to text, and returns "true" if the two strings are identical, "false" otherwise.

"=EXACT("dog", "cat")" returns "FALSE"
EXP EXP(exponent)

The EXP function returns Euler's number (~2.718) raised to the power of the value you provide.

"=EXP(5)" returns "148.413159102577"
FIND FIND(targetText, textToSearch, startingCharacter)

The FIND function returns the number of digits before finding the first value in the second value. The function performs the search starting with the starting character.

"=FIND("type", "prototype", 1)" returns "6"
FIXED FIXED(value, significance)

The FIXED function returns a number based on a specific number of significant digits.

"=FIXED(3.141592, 3)" returns "3.142"
FLOOR FLOOR(value, factor)

The FLOOR function rounds the specified value down to the nearest multiple of the factor.

"=FLOOR(11.9, 3)" returns "9"
GT GT(value1, value2)

The GT function returns "true" if the first value is greater than the second, "false" otherwise.

"=GT(2, 2)" returns "FALSE"
GTE GTE(value1, value2)

The GTE function returns "true" if the first value is greater than or equal to the second, "false" otherwise.

"=GTE(2, 2)" returns "TRUE"
HOUR HOUR(timeValue)

The HOUR function returns the hour (from 0 to 23) of a given time object.

"=HOUR(TIME(11, 40, 59))" returns "11"
IDIV IDIV(value1, value2)

The IDIV function divides the first value by the second value and drops the remainder.

"=IDIV(3, 2)" returns "1"
IF IF(booleanValue, value1, value2)

The IF function returns the second value when the first value is true, otherwise returns the third value.

"=IF(11>10,"Accepted","Denied")" returns "Accepted"

"=IF(11<10,"Accepted","Denied")" returns "Denied"
INDEX INDEX(column, row)

The INDEX function returns a specific item in an array.

"=INDEX(Table.Column_1, 3)" returns the third item in column 1.
ISEVEN ISEVEN(value)

The ISEVEN function returns "true" if the value is even, otherwise "false".

"=ISEVEN(2)" returns "true"
ISODD ISODD(value)

The ISODD function returns "true" if the value is odd, otherwise "false".

"=ISODD(2)" returns "false"
JOIN JOIN(delimiter, array)

The JOIN function concatenates the values in an array into one string. Each value is separate by the delimiter. Also see CONCATENATE.

"=JOIN("", ["L", "a", "s", "e", "r"])" returns "Laser"
LARGE LARGE(NumbersList, n)

The LARGE function returns the nth largest value in a list of numbers.

"=LARGE([10, 5, 7, 2], 2)" returns "7" because it is the second largest number
LEFT LEFT(string, [characters])

The LEFT function returns all the characters from the left up to a specific number of characters.

"=LEFT("Laserfiche", 2)" returns "La"
LEN LEN(text)

The LEN function returns the length of text.

"=LEN("Laserfiche")" returns "10"

LN LN(number)

The LN function returns the natural logarithm of a value.

"=LN(3)" returns "1.09861228866811"
LOG LOG(number, base)

The LOG function returns the logarithm of a value for a specific base.

"=LOG(1000, 10)" returns "3"
LOG10 LOG10(number)

The LOG10 function returns the logarithm of a value for base 10.

"=LOG10(1000)" returns "3"
LOWER LOWER(text)  

The LOWER function converts a text value into lowercase.

"=LOWER("POLICY")" returns "policy"
LT LT(value1, value2)

The LT function returns "true" if the first value is less than the second, "false" otherwise.

"=LT(2, 2)" returns "FALSE"
LTE LTE(value1, value2)

The LTE function returns "true" if the first value is less than or equal to the second, "false" otherwise.

"=LTE(2, 2)" returns "TRUE"
MAX MAX(value1, value2, ...)

The MAX function returns the maximum numerical value in a list of values. The function ignores non-numeric values.

"=MAX(1, 2, 4, 1, 2)" returns "4"
MAXA MAXA(value1, value2,...)

The MAXA function returns the maximum value in a list of values. The function considers text values and the Boolean "false" as 0, and the Boolean "true" as 1.

"=MAXA(-1, 0, "abc", "true")" returns "0"

"=MAXA(-1, 0, "abc", true)" returns "1" because "true" does not have quotation marks, so it is treated as a Boolean.
MEDIAN MEDIAN(value1, value2,...)

The MEDIAN function returns the middle value in a list of values. When the list contains an even number of values, the function returns the average of the two middle values.

"=MEDIAN(3, 2, 5, 1, 4, 6)" returns "3.5"
MID MID(text, startingCharacter, length)  

The MID function extracts a part of text starting with the first character and ending after a specified length.

"=MID("PO 1234 EZ", 4, 4)" returns "1234"
MIN MIN(value1, value2 ,...)

The MIN function returns the minimum numerical value in a list of values. The function ignores non-numeric values.

"=MIN(10, 3, -5)" returns "-5"
MINA MINA(value1, value2, ...)

The MINA function returns the minimum value in a list of values. The function considers text values and the Boolean "false" as 0, and the Boolean "true" as 1.

"=MINA(1, 3, "abc")" returns "0"
MINUS MINUS(value)

The MINUS function returns the opposite of the current number sign.

"=MINUS(1)" returns "-1"

" =MINUS(-1)" returns "1"
MINUTE MINUTE(timeValue)

The MINUTE function returns the minute value in a time object.

"=MINUTE(TIME(12, 30, 01))" returns "30"
MOD MOD(value1, value2)

The MOD function returns the remainder from dividing the first number by the second. Learn more

"=MOD(33, 24)" returns "9"

MODE MODE(value1, value2,...)

The MODE function returns the most frequent value in a list of values.

"=MODE(1, 3, 1, 4, 6)" returns "1"
MONTH MONTH(dateValue)

The MONTH function returns the month (from 1 to 12) in a date object.

"=MONTH(DATE(2017, 1, 20))" returns "1"
MROUND MROUND(value, multiple)

The MROUND function returns the first value to the nearest multiple of the second value.

"=MROUND(10.9, 3)" returns "12"
MULT MULT(value1, value2)

The MULT function returns the first value multiplied by the second.

"=MULT(3, 4)" returns "12"
NEQ NEQ(value1, value2)

The NEQ function returns "true" if the two values are not equal, "false" otherwise.

"=NEQ(2, 2)" returns "false"
NETWORKDAYS NETWORKDAYS(startDate, endDate, [holidays], [weekDays])

The NETWORKDAYS function returns the whole number of work days between two date objects.

"=NETWORKDAYS(DATE(2017, 1, 6), DATE(2017, 1, 14))" returns "6"

" =NETWORKDAYS(DATE(2022,10,17),DATE(2022,10,23),[DATE(2022,10,19),DATE(2022,10,20)])" returns "3"

"=NETWORKDAYS(DATE(2022,10,17),DATE(2022,10,23),[DATE(2022,10,19),DATE(2022,10,20)],[0,0,0,0,0,0,0])" returns "5"
NOT NOT(value)

The NOT function returns "true" if the value is false, or "false" if the value is true.

"=NOT(false)" returns "true"
NOW NOW()

The NOW function returns the current date and time.

"=SECOND(NOW())" returns "42"
ODD ODD(value)

The ODD function returns the value rounded to the nearest odd number, away from zero.

"=ODD(2)" returns "3"

"=ODD(-2)" returns "-3"
OR OR(value1, value2, ...)

The OR function returns "true" if any of the specified values are true, "false" otherwise.

"=OR(TRUE, FALSE, TRUE)" returns "TRUE"
PI PI()

The PI function returns the approximate value of Pi.

"=PI()" returns "3.14159265358979"
POWER POWER(value, exponent)

The POWER function returns the first value raised to the power of the second value.

"=POWER(2,2)" returns "4"
PRODUCT PRODUCT(value1, value2,...)

The PRODUCT function calculates the product of all numeric values in a list of values.

"=PRODUCT(1, 2, 3, "abc", 4)" returns "24"
PROPER PROPER(text)

The PROPER function returns the specified text value with the first letter in a text string and any other letters in the string that follow any character other than a letter converted to uppercase.

"=PROPER("laserfiche")" returns "Laserfiche"

"=PROPER("laserfiche forms")" returns "Laserfiche Forms"
QUOTIENT QUOTIENT(value1, value2)

The QUOTIENT function returns the integer from dividing the first value by the second.

"=QUOTIENT(3, 2)" returns "1"
REPLACE REPLACE(old_string, start_position, count, new_string)

The REPLACE function returns a text value from modifying the first value at the location from the second value to the third value based on the fourth value.

"=REPLACE("Laserfiche", 6, 5, "fish")" returns "Laserfish"
REPT REPT(text, number)  

The REPT function returns a specified text value repeated by a number of times.

"=REPT("Laserfiche", 2)" returns "LaserficheLaserfiche"
RIGHT RIGHT(value, n)

The RIGHT function returns all the characters from the right up to a specific number of characters.

"=RIGHT("Laserfiche", 5)" returns "fiche"
ROUND ROUND(value, places)

The ROUND function returns the value rounded to the specified number of decimal places.

"=ROUND(543.21, 0)" returns "543"
ROUNDDOWN ROUNDDOWN(value, places)

The ROUNDDOWN function returns the value rounded down to the specified number of decimal places.

"=ROUNDDOWN(567.79, 0)" returns "567"
ROUNDUP ROUNDUP(value, places)

The ROUNDUP function returns the value rounded up to the specified number of decimal places.

"=ROUNDUP(543.21, 0)" returns "544"
ROW ROW()

The ROW function returns the row number of a cell in a table. The first cell has a value of 1.

"=ROW()" returns "1" for a cell in row 1

" =ROW()=0" returns "true" if the field is outside of the table/collection
SECOND SECOND(timeValue)

The SECOND function returns the second (from 0 to 59) of a time object.

"=SECOND(TIME(12, 01, 09))" returns "9"
SIGN SIGN(value)

The SIGN function returns "1" if the specified value is greater than zero, "-1" if it is less than zero, and "0" if it is zero.

"=SIGN(-5)" returns "-1"
SPLIT SPLIT(textValue, textSplit)

The SPLIT function returns an array of values, where the first value is split up based on the method for splitting.

"=SPLIT("Text.Split.Up", ".")" returns "Text,Split,Up"
SQRT SQRT(value)

The SQRT function calculates the square root of the specified value.

"=SQRT(9)" returns "3"

"=SQRT(ABS(-9))" returns "3"
SUB SUB(value1, value2)

The SUB function returns the difference between the first and second values.

"=SUB(3, 2)" returns "1"
SUBSTITUTE SUBSTITUTE(text, target text, replacement text, number)  

The SUBSTITUTE function replaces existing text values with updated text values. The "number" parameter is optional, and specifies the position of found instances to replace if there is more than one.

"=SUBSTITUTE("Employees: Susan Marks, Paul Smith, Eric Marks", "Marks", "Johnson", 1)" returns "Employees: Susan Johnson, Paul Smith, Eric Marks"

"=SUBSTITUTE("Employees: Susan Marks, Paul Smith, Eric Marks", "Marks", "Johnson", 2)" returns "Employees: Susan Marks, Paul Smith, Eric Johnson"
SUM SUM(value1, value2, ...)

The SUM function calculates the sum of all the number values in a list.

=SUM(1, 2, 3) returns 6

=SUM(VALUE(Single_Line_1), VALUE(Single_Line_2))

returns the sum of two single line fields.

=SUM(INDEX(Table_VariableName.Column_VarName1,ROW()),INDEX(Table_VariableName.Column_VarName2,ROW()))

returns the sum of the contents of the first two columns in three columns, when this formula is placed in the third column
SUMIF SUMIF([value1,value2,...],"condition", [sum_range])

The SUMIF function calculates the sum of all the number values in a list that satisfy the condition.

=SUMIF([1, 2, 3, 4, 5], ">3") returns 9

=SUMIF( table_variable_name.column_variable_name, ">5", table_variable_name.column2_variable_name)

returns the sum of the values in column2 that are in the same row as the values greater than 5 in column1
SUMIFS SUMIFS(reference, range1, criterion1, range2, criterion2, ...)

=SUMIFS([1,1,3], [3,2,3], ">2", [4,5,6], "<6")

returns "1", because the first value in range 1 is "3" and the first value in range 2 is "4". "3" fits criterion 1 because 3 is greater than 2 and "4" fits criterion 2 because 4 is less than 6, so "1" (in the reference) is a hit. Similarly, the second "1" is a hit. However, "3" is not a hit because 6 is not less than 6 in the second range.
T T(value)

The T function returns a string as text.

"=T("1")" returns "1"

" =T(1)" returns nothing
TEXT TEXT(number)  

The TEXT function converts a specified value to a string.

"=TEXT(ABS(3-5))" returns "2"
TIME TIME(hours, minutes, seconds)

The TIME function returns the time as a fraction of a day. You can use this fraction in various other functions, including SECOND, MINUTE, and HOUR.

"=TIME(21, 20, 03)" returns "0.888923611111111"
TIMEVALUE TIMEVALUE(timestring)

The TIMEVALUE function returns the time as a fraction of a day. Inputs can be in the form "HH:MM", "HH:MM:SS", "YYYY-MM-DD HH:MM:SS", or "YYYY-MM-DD HH:MM".

"=TIMEVALUE("12:15")" returns "0.510416666666667"
TRIM TRIM(text)  

The TRIM function removes spaces before and after a specified value.

" =TRIM(" Paul ")" returns "Paul"
TRUNC TRUNC(value1, value2)  

The TRUNC function shortens (or "truncates") the first value to the number of decimal places in the second value. If the second value is absent or set to zero, the function shortens to an integer.

"=TRUNC(33.333, 2)" returns "33.33"
UPPER UPPER(text)  

The UPPER function converts the specified text to all uppercase letters.

"=UPPER("ucsd")" returns "UCSD"
VALUE VALUE(input)

The VALUE function converts a specified value to a number.

"=VALUE("12:00:00")" returns "0.5"

" =VALUE("1")" returns "1"

"=VALUE("1/16/2017")" returns "42751"

WEEKDAY WEEKDAY(date, [type])

The WEEKDAY function returns the day of the week for the specified date as a number. You can type "1", "2", or "3" to determine whether the first day of the week is a Sunday or Monday.

"=WEEKDAY("4/15/2016", 3)" returns "4"

WEEKNUM WEEKNUM(date, mode)

The WEEKNUM function returns the number of the week in a year for a specified date object.

"=WEEKNUM(DATE(2017, 1, 8))" returns "2" because the date is in the first week of the year

"=WEEKNUM(DATE(2017, 1, 8), 2)" returns "1" because the "2" indicates the counting starts on a Monday.
WORKDAY WORKDAY(startDay, numDays)

The WORKDAY function returns a date object based on the number of days after a start day.

"=WORKDAY(DATE(2017, 2, 16), 1)" returns "42783"

"=DATE(2017,2,16)" returns "42782"

YEAR YEAR(dateValue)

The YEAR function returns the year of a given date object.

"=YEAR(DATE(2017,1,16))" returns "2017"

YEARFRAC YEARFRAC(startDate, endDate, [basis])

Computes the fraction of the number of years between a startDate and endDate. Basis{0,1} uses US (NASD) 30/360 and Actual/365 to compute days. Excluding the [basis] defaults to "0".

"=YEARFRAC(DATE(2017, 1, 17), DATE(2017, 1, 18))" returns "0.00277777777777778"

"=YEARFRAC(DATE(2017,1,17),DATE(2017,1,18),1)" returns "0.00273972602739726"