- [ALL FORMULAS]
- Advanced Math
- Choices
- Contextual Formulas
- Data Conversion
- Data Sources
- Date / Time
- Excel Financial Formulas
- Lists / Sets of Values
- Location
- Logic
- Math
- Process Steps
- Repeats / Tables
- Text

Returns the accrued interest for a security that pays periodic interest.

https://support.office.com/en-us/home

” data-title=”Returns the accrued interest for a security that pays interest at maturity.

https://support.office.com/en-us/home

Returns the depreciation for each accounting period by using a depreciation coefficient.

https://support.office.com/en-us/home

Returns the depreciation for each accounting period.

https://support.office.com/en-us/home

Returns the number of days from the beginning of the coupon period to the settlement date.

https://support.office.com/en-us/home

Returns the number of days in the coupon period that contains the settlement date.

https://support.office.com/en-us/home

Returns the number of days from the settlement date to the next coupon date.

https://support.office.com/en-us/home

Returns the next coupon date after the settlement date.

https://support.office.com/en-us/home

Returns the number of coupons payable between the settlement date and maturity date.

https://support.office.com/en-us/home

Returns the previous coupon date before the settlement date.

https://support.office.com/en-us/home

Returns the cumulative interest paid between two periods.

https://support.office.com/en-us/home

Returns the cumulative principal paid on a loan between two periods.

https://support.office.com/en-us/home

Returns the depreciation of an asset for a specified period by using the fixed-declining balance method.

https://support.office.com/en-us/home

Returns the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify.

https://support.office.com/en-us/home

Returns the discount rate for a security.

https://support.office.com/en-us/home

Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number.

https://support.office.com/en-us/home

Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction.

https://support.office.com/en-us/home

Returns the annual duration of a security with periodic interest payments.

https://support.office.com/en-us/home

Returns the effective annual interest rate.

https://support.office.com/en-us/home

Returns the future value of an investment.

https://support.office.com/en-us/home

Returns the future value of an initial principal after applying a series of compound interest rates.

https://support.office.com/en-us/home

Returns the interest rate for a fully invested security.

https://support.office.com/en-us/home

Returns the interest payment for an investment for a given period.

https://support.office.com/en-us/home

Returns the internal rate of return for a series of cash flows.

https://support.office.com/en-us/home

Calculates the interest paid during a specific period of an investment.

https://support.office.com/en-us/home

Returns the Macauley modified duration for a security with an assumed par value of $100.

https://support.office.com/en-us/home

Returns the internal rate of return where positive and negative cash flows are financed at different rates.

https://support.office.com/en-us/home

Returns the annual nominal interest rate.

https://support.office.com/en-us/home

Returns the number of periods for an investment.

https://support.office.com/en-us/home

Returns the net present value of an investment based on a series of periodic cash flows and a discount rate.

https://support.office.com/en-us/home

Returns the periodic payment for an annuity.

https://support.office.com/en-us/home

Returns the payment on the principal for an investment for a given period.

https://support.office.com/en-us/home

Returns the price per $100 face value of a security that pays periodic interest.

https://support.office.com/en-us/home

Returns the price per $100 face value of a discounted security.

https://support.office.com/en-us/home

Returns the price per $100 face value of a security that pays interest at maturity.

https://support.office.com/en-us/home

Returns the present value of an investment.

https://support.office.com/en-us/home

Returns the interest rate per period of an annuity.

https://support.office.com/en-us/home

Returns the amount received at maturity for a fully invested security.

https://support.office.com/en-us/home

Returns the straight-line depreciation of an asset for one period.

https://support.office.com/en-us/home

Returns the sum-of-years’ digits depreciation of an asset for a specified period.

https://support.office.com/en-us/home

Returns the bond-equivalent yield for a Treasury bill.

https://support.office.com/en-us/home

Returns the price per $100 face value for a Treasury bill.

https://support.office.com/en-us/home

Returns the yield for a Treasury bill.

https://support.office.com/en-us/home

Returns the depreciation of an asset for a specified or partial period by using a declining balance method.

https://support.office.com/en-us/home

Returns the yield on a security that pays periodic interest.

https://support.office.com/en-us/home

Returns the annual yield for a discounted security; for example, a Treasury bill.

https://support.office.com/en-us/home

Returns the net present value for a schedule of cash flows that is not necessarily periodic.

https://support.office.com/en-us/home

Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.

https://support.office.com/en-us/home

Returns the annual yield of a security that pays interest at maturity.

https://support.office.com/en-us/home

The current local date reported by the device.

NOTE: Device dates can be inaccurate if the local time is not correct.

The current local date and time reported by the device.

NOTE: Device times can be inaccurate if the local time is not correct.

The current Greenwich Mean Time (GMT) date reported by the device.

The current Greenwich Mean Time (GMT) date and time reported by the device.

User’s Email Address

User’s First Name

User’s Last Name

User’s External Id

Organization Name

Gets the Global Value for the specified key name (if any).

Gets the Provider Meta Data Value for the specified key (if any).

For example if your Organization setup contains a Meta Data key of billing_id then you may access this value across the platform with ORGMETA(‘billing_id’).

Gets the User Meta Data Value for the specified key (if any).

For example if your User setup contains a Meta Data key of billing_id then you may access this value across the platform with USERMETA(‘billing_id’).

The device name.

The device operating system.

The device operating system version.

The current version of the app installed on the user’s device.

The version number of the current screen on the device.

The date & time on which the current screen on the device was last updated for GMT (UTC) time zone.

Returns the current answer value of the given field. Useful for avoiding circular reference issues, e.g. when filtering repeated values in a Choices field based on its own current value.

Simple counter that increments by 1 every time a new Form entry is created in the app. Will left pad the counter with zero’s to the specified pad width length. Concatenate counter() with user data to generate unique numbers.

NOTE: Counter numbers are device-specific.

Gets data value for the given key when this Screen is linked to a Task. Returns blank value if no Task is linked.

The following Task keys are available:

ADDITIONALINFO, COMPLETEATLAT, COMPLETEATLON, EXTERNALID, ID, ISOVERDUE, NAME, PERFORMWITHIN, COMPLETEBY, COMPLETEBYUTC, STARTTIME, STARTTIMEUTC

e.g. TASK(‘NAME’) returns the linked Task’s name value.

Addition operator.

NOTE: Always put a space on either side of the ‘+’

Subtraction operator.

NOTE: Always put a space on either side of the ‘-‘

Multiplication operator.

NOTE: Always put a space on either side of the ‘*’

Division operator.

NOTE: Always put a space on either side of the ‘DIV’

Modulo operator.

NOTE: Always put a space on either side of the ‘MOD’

Generates a random number or string. Can be called with 0 or 1 parameter

RANDOM() returns a decimal number between 0 and 1.0

RANDOM(length) returns random integer of given length

Rounds the given number to the specified number of fractional places.

Returns the given number to the specified power.

Truncates given number value to an integer.

Effectively rounds number down to zero decimal places.

Returns the larger of two numbers.

Returns the smaller of two numbers.

Returns the smallest integer value that is greater than or equal to the specified number.

Returns the largest integer less than or equal to the specified number.

Returns the number of characters in the given value.

Retrieves a substring from the given value.

Substrings start at the zero-indexed start position and run to the end of the val unless an optional character length is specified.

e.g. if myfield has value ‘ABCDEF’, then:

SUBSTR({{myfield}}, 2) gives CDEF

SUBSTR({{myfield}}, 2, 1) gives C

Joins the given values end-to-end.

Joins the given values end-to-end, separated by the given separator.

Substitute’s new_text for old_text into the given value.

e.g. if myfield has value ‘ABC|DEF’, then:

SUBSTITUTE({{myfield}}, ‘|’, ‘\n’) replaces | with new line

Converts all characters in the specified val to lower case

e.g. LOWER({{myfield}})

Converts all characters in the specified val to upper case

e.g. UPPER({{myfield}})

Returns true or false result depending on if the given starts with text appears at the beginning of the given text value. Matching is case insensitive.

e.g. if myfield has value ‘ABCDEF’, then:

STARTSWITH({{myfield}}, ‘ABC’) result is true

Returns true or false result depending on if the given contains text appears anywhere within the given text value. Matching is case insensitive.

e.g. if myfield has value ‘ABCDEF’, then:

CONTAINS({{myfield}}, ‘CDE’) result is true

Gets the zero-based position for the first occurrence of the given value in the input text. Returns -1 if the search value is not found.

Optional start index will begin search at given zero-based index.

Optional count specifies how many characters to search within from the start index.

e.g. INDEXOF(‘AAA|BBB|CCC’, ‘A’) returns 0

e.g. INDEXOF(‘AAA|BBB|CCC’, ‘BD’) is -1

e.g. INDEXOF(‘AAA|BBB|CCC’, ‘B’, 5) is 5

e.g. INDEXOF(‘AAA|BBB|CCC’, ‘|’, 4, 3) is 7

Generates a random string of characters of the given length.

Generates a new Globally Unique Identifier

https://en.wikipedia.org/wiki/Globally_unique_identifier

Returns a new Date/Time that adds the specified number of units to the specified starting date value.

e.g. DATEADD({{mydatefield}}, 6, ‘MM’)

Unit specifiers are:

YY – whole years

MM – whole months

DD – whole days

HH – whole hours

MI – whole minutes

SS – whole seconds

e.g. DATEDIFF({{mydatefield}}, now(), ‘HH’)

Unit specifiers are:

YY – whole years

MM – whole months

DD – whole days

HH – whole hours

MI – whole minutes

SS – whole seconds

Converts the given UTC date time value to local date time.

When used in a Form Design the local time is based on the device’s local time. If used in a template, local time is based on the Organization’s Default time zone.

e.g. DATETOLOCAL({{mydatefield}}

Converts the given UTC date time value to local date time.

When used in a Form Design the local time is based on the device’s local time. If used in a template, local time is based on the Organization’s Default time zone.

e.g. DATETOLOCAL({{mydatefield}})

Returns the year portion of the specified date value.

e.g. YEAR({{mydatefield}})

Returns the month portion of the specified date value.

e.g. MONTH({{mydatefield}})

Returns the day portion of the specified date value.

e.g. DAY({{mydatefield}})

Returns the hours portion of the specified date value.

e.g. HOUR({{mydatefield}})

Returns the minutes portion of the specified date value.

e.g. MINUTE({{mydatefield}})

Returns the seconds portion of the specified date value.

e.g. SECOND({{mydatefield}})

Returns the numbered day of the year for the specified date value.

Values returned are between 1 and 366.

e.g. DAYYEAR({{mydatefield}})

Returns true if both operands are equal.

Returns true if the first operand is less than the second.

Returns true if the first operand is greater than the second.

Returns true if any one of the operands is true.

Returns true if both of the operands is true.

Returns true if the value given is false, and false if the value given is true.

Returns true.

Returns false.

Let’s you return one of two values based on whether the given condition is true or false. Useful for toggling a field’s dynamic value based on previous answers.

e.g. IF({{score}} > 50, ‘YOU PASS’, ‘YOU FAIL’)

Returns true/false based on whether the given value is blank/empty.

An easy way to check if a field has no answer.

Returns true/false based on whether the given value is not blank/empty.

An easy way to check if a field has any answer.

Use this function when you want to guarantee a non-blank value is returned. This is useful when doing scoring calculations – wrap each answer in a COALESCE({{answer}}, 0) to ensure you get a zero if the user has not provided an answer.

Returns true/false based on whether the regular expression finds a match in the input string. Regular expressions are a very powerful, advanced feature. Learn about regular expressions.

https://msdn.microsoft.com/en-us/library/hs600312(v=vs.110).aspx

Replaces the text matched by the given regular expression with the text specified in the replacement string. Note that the regular expression pattern can be static text or passed in from another field.

e.g. REPLACE({{input}}, ‘ab*c’, ‘_’)

e.g. REPLACE({{input}}, {{regex}}, ‘_’)

Convert a date/time to formatted string value.

e.g. FORMAT-DATE(now(), ‘MM/dd/yy H:mm:ss’)

outputs like: 06/10/11 15:24:16

Typical format specifies include:

yy – 2 digit year

yyyy – 4 digit year

MM – 2 digit month

MMM – 3 character abbreviated month

dd – 2 digit day

HH – hour in 24 hour clock

mm – 2 digit minute (00-59)

ss – 2 digit second (00-59)

Returns true if any one of the operands is true.

Returns true if both of the operands is true.

Returns true if the value given is false, and false if the value given is true..

Returns true.

Returns false.

Convert a number to a formatted string value.

By default, US formatting is applied; the optional culture parameter lets you specify the target format culture.

e.g. FORMAT-NUM({{numfield}}, ‘00.00’) outputs 4.9675 as: 04.97

e.g. FORMAT-NUM({{numfield}}, ‘00.00’, ‘fr-FR’)

outputs 4.9675 as: 04,97

Typical format specifies include:

0 – Replaces with digit or zero if none

# – Replaces with digit or nothing if none

. – Sets the decimal separator position

, – Sets grouping operator position

Converts the given value to a date value.

Converts the given value to a string value.

Converts the given value to an integer value.

Converts the given value to a numerical value.

Converts the given value to a boolean value.

Returns a Unicode checkbox that is ticked or crossed, depending on whether val equals matchTo.

e.g. CBOX({{myfield}}, ‘Yes’)

outputs ☑ or ☒

Returns a Unicode checkbox that is ticked or blank, depending on whether val equals matchTo.

e.g. CBOXB({{myfield}}, ‘Yes’)

outputs ☑ or ☐

Generates the web URL to the given image field.

Useful for assignment into a Data Source image column or for providing direct download links in Connector outputs.

Counts rows in the given Data Source, optionally filtering rows by formula. Refer to the Data Source by it’s External Id (from the Data Source -> Settings page)

e.g. DSCOUNT(‘STAFF’)

Add a filter formula using {{this[column]}} to refer to columns.

e.g. DSCOUNT(‘STAFF’, ‘{{this[2]}} = “BOB”‘)

counts rows where 3rd column = BOB

Sums column values in the given Data Source, optionally filtering rows by formula. Refer to the Data Source by it’s External Id (from the Data Source -> Settings page).

Refer to the column by it’s zero-based index.

e.g. DSSUM(‘STAFF’, 2)

sums the 3rd column’s values

Add a filter formula using {{this[column]}} to refer to columns.

e.g. DSSUM(‘STAFF’, 2, ‘{{this[5]}} = “BOB”‘)

sums 3rd column where 6th column = BOB

Averages column values in the given Data Source, optionally filtering rows by formula. Refer to the Data Source by its External Id (from the Data Source -> Settings page). Refer to the column by its zero-based index.

e.g. DSAVG(‘STAFF’, 2)

averages the 3rd column’s values

Add a filter formula using {{this[column]}} to refer to columns.

e.g. DSAVG(‘STAFF’, 2, ‘{{this[5]}} = “BOB”‘)

averages 3rd column where 6th column = BOB

Gets maximum column value in the given Data Source, optionally filtering rows by formula. Refer to the Data Source by it’s External Id (from the Data Source -> Settings page). Refer to the column by it’s zero-based index.

e.g. DSMAX(‘STAFF’, 2)

gets 3rd column’s max value

Add a filter formula using {{this[column]}} to refer to columns.

e.g. DSMAX(‘STAFF’, 2, ‘{{this[5]}} = “BOB”‘)

maxes 3rd column where 6th column = BOB

Gets minimum column value in the given Data Source, optionally filtering rows by formula. Refer to the Data Source by it’s External Id (from the Data Source -> Settings page). Refer to the column by it’s zero-based index.

e.g. DSMIN(‘STAFF’, 2)

gets 3rd column’s max value

Add a filter formula using {{this[column]}} to refer to columns.

e.g. DSMIN(‘STAFF’, 2, ‘{{this[5]}} = “BOB”‘)

mins 3rd column where 6th column = BOB

Gets first column value in the given Data Source, optionally filtering rows by formula. Refer to the Data Source by it’s External Id (from the Data Source -> Settings page). Refer to the column by it’s zero-based index.

e.g. DSFIRST(‘STAFF’, 2)

gets 3rd column’s first value

Add a filter formula using {{this[column]}} to refer to columns.

e.g. DSFIRST(‘STAFF’, 2, ‘{{this[5]}} = “BOB”‘)

first of 3rd column where 6th column = BOB

Gets last column value in the given Data Source, optionally filtering rows by formula. Refer to the Data Source by it’s External Id (from the Data Source -> Settings page). Refer to the column by it’s zero-based index.

e.g. DSLAST(‘STAFF’, 2)

gets 3rd column’s last value

Add a filter formula using {{this[column]}} to refer to columns.

e.g. DSLAST(‘STAFF’, 2, ‘{{this[5]}} = “BOB”‘)

last of 3rd column where 6th column = BOB

Collects answers from fields with data names matching the given regular expression pattern. List results are typically used with functions like SUM()/COUNT() to calculate results on fields that follow a common naming convention – e.g. survey fields like q1, q2, q3 etc.

Second optional parameter applies a filter formula to the answers gathered, leaving only answers that pass the condition. Use {{this}} to refer to the answer value in formula.

e.g. SUM(LIST(‘q[0-9]+’)) sums answers for fields named q1, q2 etc

e.g. COUNT(LIST(‘q[0-9]+’, ‘{{this}} = 5’)) counts q1,q2 etc fields where answers equal to 5

Converts the given value to a List. The value must be text containing delimited List elements – e.g. 34|76|9 Second optional parameter is the delimiter character separating elements. Default is pipe character.

Third optional parameter applies a filter formula to the List elements gathered, leaving only elements that pass the condition. Use {{this}} to refer to the element value in formula.

e.g. TOLIST({{myfield}})

e.g. TOLIST({{myfield}}, ‘STARTSWITH({{this}}, “B”)’)

e.g. TOLIST(‘3,6,9,62’, ‘,’, ‘{{this}} > 5’))

Returns true if the given value is found within the given List.

e.g. IN(‘ABC’, LIST(‘q[0-9]+’))

e.g. IN(‘ABC’, PRIOR(‘repeatfield’))

Returns true if the given value is NOT found within the given List.

e.g. NOTIN(‘ABC’, TOLIST({{listfield}}))

e.g. NOTIN(‘ABC’, PRIOR(‘repeatfield’))

Counts the values in the given List. Parameter must be a valid List function such as LIST() or PRIOR()

e.g. COUNT(LIST(‘q[0-9]+’))

Sums the values in the given numeric List. Parameter must be a valid List function such as LIST() or PRIOR()

e.g. SUM(LIST(‘q[0-9]+’))

Averages the values in the given numeric List. Parameter must be a valid List function such as LIST() or PRIOR()

e.g. AVERAGE(LIST(‘q[0-9]+’))

Gets minimum value in the given numeric List. Parameter must be a valid List function such as LIST() or PRIOR()

e.g. MIN(LIST(‘q[0-9]+’))

Gets the maximum value in the given numeric List. Parameter must be a valid List function such as LIST() or PRIOR()

e.g. MAX(LIST(‘q[0-9]+’))

Gets the first value in the given List. Parameter must be a valid List function such as LIST() or PRIOR()

e.g. FIRST(LIST(‘q[0-9]+’))

Gets the last value in the given List. Parameter must be a valid List function such as LIST() or PRIOR()

e.g. LAST(LIST(‘q[0-9]+’))

The page/row number of the current repeat Page or Table row.

Useful for generating incremental numbers for sections/clauses (e.g. 1.1, 1.2, 1.3)

Parameter is the data name of the repeatable page or table.

e.g. POSITION({{repeatpage}})

Gets the previous repeat/row value(s) of the named field within a Page/Table, starting from the current repeat context. Should only be used within a repeatable Page or Table. Optional second parameter specifies how many prior answers to retrieve. Prior repeat values are returned as a List unless the second parameter is 1; if so the value of the last prior answer is returned. Useful for performing aggregate functions on repeat values thus far (e.g. SUM,COUNT) and, if second parameter is 1, for copying forward the previous repeat/row value into the current new instance.

e.g. PRIOR(‘myfield’) List of all prior answers

e.g. PRIOR(‘myfield’, 1) last prior value only

Counts the repeats/rows of a Page/Table.

Useful for counting rows/repeats captured – e.g. an order line count

e.g. COUNT({{repeatPage}})

Sums a Number field across all repeats/rows of a Page/Table.

Useful for totalling values captured – e.g. an order line total

e.g. SUM({{numberfield}})

Averages a Number field across all repeats/rows of a Page/Table.

Useful for aggregating values captured – e.g. an average quantity

e.g. AVERAGE({{numberfield}})

Sums the values in the given numeric List. Parameter must be a valid List function such as LIST() or PRIOR()

e.g. SUM(LIST(‘q[0-9]+’))

Averages the values in the given numeric List. Parameter must be a valid List function such as LIST() or PRIOR()

e.g. AVERAGE(LIST(‘q[0-9]+’))

Maximum value of a Number field across all repeats/rows of a Page/Table.

e.g. MAX({{mynumberfield}})

Minimum value of a Number field across all repeats/rows of a Page/Table.

e.g. MIN({{mynumberfield}})

Value of the first occurrence/row of a repeatable Page or Table field.

e.g. FIRST({{myrepeatfield}})

Value of the last occurrence/row of a repeatable Page or Table field.

e.g. LAST({{myrepeatfield}})

Returns true if the value given is selected in the given choices field, false otherwise.

Returns the number of choices selected on the given choices field.

Returns the latitude in decimal degrees of the given location value.

Returns the longitude in decimal degrees of the given location value.

Returns the heading against true north in decimal degrees of the given location value.

Returns the altitude above/below sea level in metres of the given location value.

Returns the accuracy in metres of the given location value.

Returns the street number for the given location value.

Matches ‘sub_thoroughfare’ on OASIS Specification.

Returns the street name for the given location value.

Matches ‘thoroughfare’ on OASIS Specification.

Returns the city / locality name for the given location value.

Matches ‘locality’ on OASIS Specification.

Returns the county / district area for the given location value. Mainly USA specific.

Matches ‘sub_admin_area’ on OASIS Specification

Returns the state / province for the given location value.

Matches ‘admin_area’ on OASIS Specification.

Returns the postal / zip code of the given location value.

Matches ‘postal_code’ on OASIS Specification.

Returns the ISO country code of the given location value.

Matches ‘country’ on OASIS Specification.

Finds the miles between two geo-points, using great-circle math.

Geo-points are strings in ‘lat lon’ format, Location field answers are also geo-points.

e.g. MIBETWEEN(‘-8.45234 27.7623423’, {{myGpsField}})

Finds the kilometres between two geo-points, using great-circle math.

Geo-points are strings in ‘lat lon’ format, Location field answers are also geo-points.

e.g. KMBETWEEN(‘-8.45234 27.7623423’, {{myGpsField}})

Returns a true/false answer on whether the given geo-point is within the given polygon.

Polygon values must be pipe-seperated string of geo-points.

e.g. INPOLYGON({{myGpsVal}}, ‘-8.6782523 27.2918257|-8.6672229 28.7094422|-7.6447228 29.3849982’)

Returns the name of the current Step in the process. If no Step has occurred yet, then this function returns a blank value.

Returns true if the named Process Step field is the current Step in the process. If no Step has occurred yet, this function returns true for ANY Process Step name.

Returns the result of the named Process Step field, if any is set.

Returns the email of the user that decided the result (if any) of the named Process Step field.

Returns the first name of the user that decided the result (if any) of the named Process Step field.

Returns the last name of the user that decided the result (if any) of the named Process Step field.

Returns the number 3.14159265358979, the mathematical constant Pi, accurate to 15 digits.

Converts radians to degrees.

Converts degrees to radians.

Returns a number which, when multiplied by it self, will produce the given value.

Returns the exponent to which the given base must be raised to produce the given value.

Returns sine of given radian angle value.

If your value is in degrees, multiply it by PI()/180 or use the RADIANS function to convert it to radians.

Returns cosine of given radian angle value.

If your value is in degrees, multiply it by PI()/180 or use the RADIANS function to convert it to radians.

Returns tangent of given radian angle value.

If your value is in degrees, multiply it by PI()/180 or use the RADIANS function to convert it to radians.

Returns the arcsine, or inverse sine, of a number.

The returned angle is given in radians in the range -pi/2 to pi/2.

Returns the arccosine, or inverse cosine, of a number.

The returned angle is given in radians in the range 0 (zero) to pi.

Returns the arctangent, or inverse tangent, of a number.

The returned angle is given in radians in the range -pi/2 to pi/2.

Returns hyperbolic sine of given radian angle value.

Returns hyperbolic cosine of given radian angle value.

Returns hyperbolic tangent of given radian angle value.

Returns the accrued interest for a security that pays periodic interest.

https://support.office.com/en-us/home

” data-title=”Returns the accrued interest for a security that pays interest at maturity.

https://support.office.com/en-us/home

Returns the depreciation for each accounting period by using a depreciation coefficient.

https://support.office.com/en-us/home

Returns the depreciation for each accounting period.

https://support.office.com/en-us/home

Returns the number of days from the beginning of the coupon period to the settlement date.

https://support.office.com/en-us/home

Returns the number of days in the coupon period that contains the settlement date.

https://support.office.com/en-us/home

Returns the number of days from the settlement date to the next coupon date.

https://support.office.com/en-us/home

Returns the next coupon date after the settlement date.

https://support.office.com/en-us/home

Returns the number of coupons payable between the settlement date and maturity date.

https://support.office.com/en-us/home

Returns the previous coupon date before the settlement date.

https://support.office.com/en-us/home

Returns the cumulative interest paid between two periods.

https://support.office.com/en-us/home

Returns the cumulative principal paid on a loan between two periods.

https://support.office.com/en-us/home

Returns the depreciation of an asset for a specified period by using the fixed-declining balance method.

https://support.office.com/en-us/home

Returns the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify.

https://support.office.com/en-us/home

Returns the discount rate for a security.

https://support.office.com/en-us/home

Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number.

https://support.office.com/en-us/home

Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction.

https://support.office.com/en-us/home

Returns the annual duration of a security with periodic interest payments.

https://support.office.com/en-us/home

Returns the effective annual interest rate.

https://support.office.com/en-us/home

Returns the future value of an investment.

https://support.office.com/en-us/home

Returns the future value of an initial principal after applying a series of compound interest rates.

https://support.office.com/en-us/home

Returns the interest rate for a fully invested security.

https://support.office.com/en-us/home

Returns the interest payment for an investment for a given period.

https://support.office.com/en-us/home

Returns the internal rate of return for a series of cash flows.

https://support.office.com/en-us/home

Calculates the interest paid during a specific period of an investment.

https://support.office.com/en-us/home

Returns the Macauley modified duration for a security with an assumed par value of $100.

https://support.office.com/en-us/home

Returns the internal rate of return where positive and negative cash flows are financed at different rates.

https://support.office.com/en-us/home

Returns the annual nominal interest rate.

https://support.office.com/en-us/home

Returns the number of periods for an investment.

https://support.office.com/en-us/home

Returns the net present value of an investment based on a series of periodic cash flows and a discount rate.

https://support.office.com/en-us/home

Returns the periodic payment for an annuity.

https://support.office.com/en-us/home

Returns the payment on the principal for an investment for a given period.

https://support.office.com/en-us/home

Returns the price per $100 face value of a security that pays periodic interest.

https://support.office.com/en-us/home

Returns the price per $100 face value of a discounted security.

https://support.office.com/en-us/home

Returns the price per $100 face value of a security that pays interest at maturity.

https://support.office.com/en-us/home

Returns the present value of an investment.

https://support.office.com/en-us/home

Returns the interest rate per period of an annuity.

https://support.office.com/en-us/home

Returns the amount received at maturity for a fully invested security.

https://support.office.com/en-us/home

Returns the straight-line depreciation of an asset for one period.

https://support.office.com/en-us/home

Returns the sum-of-years’ digits depreciation of an asset for a specified period.

https://support.office.com/en-us/home

Returns the bond-equivalent yield for a Treasury bill.

https://support.office.com/en-us/home

Returns the price per $100 face value for a Treasury bill.

https://support.office.com/en-us/home

Returns the yield for a Treasury bill.

https://support.office.com/en-us/home

Returns the depreciation of an asset for a specified or partial period by using a declining balance method.

https://support.office.com/en-us/home

Returns the yield on a security that pays periodic interest.

https://support.office.com/en-us/home

Returns the annual yield for a discounted security; for example, a Treasury bill.

https://support.office.com/en-us/home

Returns the net present value for a schedule of cash flows that is not necessarily periodic.

https://support.office.com/en-us/home

Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.

https://support.office.com/en-us/home

Returns the annual yield of a security that pays interest at maturity.

https://support.office.com/en-us/home

The current local date reported by the device.

NOTE: Device dates can be inaccurate if the local time is not correct.

The current local date and time reported by the device.

NOTE: Device times can be inaccurate if the local time is not correct.

The current Greenwich Mean Time (GMT) date reported by the device.

The current Greenwich Mean Time (GMT) date and time reported by the device.

User’s Email Address

User’s First Name

User’s Last Name

User’s External Id

Organization Name

Gets the Global Value for the specified key name (if any).

Gets the Provider Meta Data Value for the specified key (if any).

For example if your Organization setup contains a Meta Data key of billing_id then you may access this value across the platform with ORGMETA(‘billing_id’).

Gets the User Meta Data Value for the specified key (if any).

For example if your User setup contains a Meta Data key of billing_id then you may access this value across the platform with USERMETA(‘billing_id’).

The device name.

The device operating system.

The device operating system version.

The current version of the app installed on the user’s device.

The version number of the current screen on the device.

The date & time on which the current screen on the device was last updated for GMT (UTC) time zone.

Returns the current answer value of the given field. Useful for avoiding circular reference issues, e.g. when filtering repeated values in a Choices field based on its own current value.

Simple counter that increments by 1 every time a new Form entry is created in the app. Will left pad the counter with zero’s to the specified pad width length. Concatenate counter() with user data to generate unique numbers.

NOTE: Counter numbers are device-specific.

Gets data value for the given key when this Screen is linked to a Task. Returns blank value if no Task is linked.

The following Task keys are available:

ADDITIONALINFO, COMPLETEATLAT, COMPLETEATLON, EXTERNALID, ID, ISOVERDUE, NAME, PERFORMWITHIN, COMPLETEBY, COMPLETEBYUTC, STARTTIME, STARTTIMEUTC

e.g. TASK(‘NAME’) returns the linked Task’s name value.

Addition operator.

NOTE: Always put a space on either side of the ‘+’

Subtraction operator.

NOTE: Always put a space on either side of the ‘-‘

Multiplication operator.

NOTE: Always put a space on either side of the ‘*’

Division operator.

NOTE: Always put a space on either side of the ‘DIV’

Modulo operator.

NOTE: Always put a space on either side of the ‘MOD’

Generates a random number or string. Can be called with 0 or 1 parameter

RANDOM() returns a decimal number between 0 and 1.0

RANDOM(length) returns random integer of given length

Rounds the given number to the specified number of fractional places.

Returns the given number to the specified power.

Truncates given number value to an integer.

Effectively rounds number down to zero decimal places.

Returns the larger of two numbers.

Returns the smaller of two numbers.

Returns the smallest integer value that is greater than or equal to the specified number.

Returns the largest integer less than or equal to the specified number.

Returns the number of characters in the given value.

Retrieves a substring from the given value.

Substrings start at the zero-indexed start position and run to the end of the val unless an optional character length is specified.

e.g. if myfield has value ‘ABCDEF’, then:

SUBSTR({{myfield}}, 2) gives CDEF

SUBSTR({{myfield}}, 2, 1) gives C

Joins the given values end-to-end.

Joins the given values end-to-end, separated by the given separator.

Substitute’s new_text for old_text into the given value.

e.g. if myfield has value ‘ABC|DEF’, then:

SUBSTITUTE({{myfield}}, ‘|’, ‘\n’) replaces | with new line

Converts all characters in the specified val to lower case

e.g. LOWER({{myfield}})

Converts all characters in the specified val to upper case

e.g. UPPER({{myfield}})

Returns true or false result depending on if the given starts with text appears at the beginning of the given text value. Matching is case insensitive.

e.g. if myfield has value ‘ABCDEF’, then:

STARTSWITH({{myfield}}, ‘ABC’) result is true

Returns true or false result depending on if the given contains text appears anywhere within the given text value. Matching is case insensitive.

e.g. if myfield has value ‘ABCDEF’, then:

CONTAINS({{myfield}}, ‘CDE’) result is true

Gets the zero-based position for the first occurrence of the given value in the input text. Returns -1 if the search value is not found.

Optional start index will begin search at given zero-based index.

Optional count specifies how many characters to search within from the start index.

e.g. INDEXOF(‘AAA|BBB|CCC’, ‘A’) returns 0

e.g. INDEXOF(‘AAA|BBB|CCC’, ‘BD’) is -1

e.g. INDEXOF(‘AAA|BBB|CCC’, ‘B’, 5) is 5

e.g. INDEXOF(‘AAA|BBB|CCC’, ‘|’, 4, 3) is 7

Generates a random string of characters of the given length.

Generates a new Globally Unique Identifier

https://en.wikipedia.org/wiki/Globally_unique_identifier

Returns a new Date/Time that adds the specified number of units to the specified starting date value.

e.g. DATEADD({{mydatefield}}, 6, ‘MM’)

Unit specifiers are:

YY – whole years

MM – whole months

DD – whole days

HH – whole hours

MI – whole minutes

SS – whole seconds

e.g. DATEDIFF({{mydatefield}}, now(), ‘HH’)

YY – whole years

MM – whole months

DD – whole days

HH – whole hours

MI – whole minutes

SS – whole seconds

Converts the given UTC date time value to local date time.

When used in a Form Design the local time is based on the device’s local time. If used in a template, local time is based on the Organization’s Default time zone.

e.g. DATETOLOCAL({{mydatefield}}

When used in a Form Design the local time is based on the device’s local time. If used in a template, local time is based on the Organization’s Default time zone.

e.g. DATETOLOCAL({{mydatefield}})

Returns the year portion of the specified date value.

e.g. YEAR({{mydatefield}})

Returns the month portion of the specified date value.

e.g. MONTH({{mydatefield}})

Returns the day portion of the specified date value.

e.g. DAY({{mydatefield}})

Returns the hours portion of the specified date value.

e.g. HOUR({{mydatefield}})

Returns the minutes portion of the specified date value.

e.g. MINUTE({{mydatefield}})

Returns the seconds portion of the specified date value.

e.g. SECOND({{mydatefield}})

Returns the numbered day of the year for the specified date value.

Values returned are between 1 and 366.

e.g. DAYYEAR({{mydatefield}})

Returns true if both operands are equal.

Returns true if the first operand is less than the second.

Returns true if the first operand is greater than the second.

Returns true if any one of the operands is true.

Returns true if both of the operands is true.

Returns true if the value given is false, and false if the value given is true.

Returns true.

Returns false.

Let’s you return one of two values based on whether the given condition is true or false. Useful for toggling a field’s dynamic value based on previous answers.

e.g. IF({{score}} > 50, ‘YOU PASS’, ‘YOU FAIL’)

Returns true/false based on whether the given value is blank/empty.

An easy way to check if a field has no answer.

Returns true/false based on whether the given value is not blank/empty.

An easy way to check if a field has any answer.

Use this function when you want to guarantee a non-blank value is returned. This is useful when doing scoring calculations – wrap each answer in a COALESCE({{answer}}, 0) to ensure you get a zero if the user has not provided an answer.

Returns true/false based on whether the regular expression finds a match in the input string. Regular expressions are a very powerful, advanced feature. Learn about regular expressions.

https://msdn.microsoft.com/en-us/library/hs600312(v=vs.110).aspx

Replaces the text matched by the given regular expression with the text specified in the replacement string. Note that the regular expression pattern can be static text or passed in from another field.

e.g. REPLACE({{input}}, ‘ab*c’, ‘_’)

e.g. REPLACE({{input}}, {{regex}}, ‘_’)

Convert a date/time to formatted string value.

e.g. FORMAT-DATE(now(), ‘MM/dd/yy H:mm:ss’)

outputs like: 06/10/11 15:24:16

Typical format specifies include:

yy – 2 digit year

yyyy – 4 digit year

MM – 2 digit month

MMM – 3 character abbreviated month

dd – 2 digit day

HH – hour in 24 hour clock

mm – 2 digit minute (00-59)

ss – 2 digit second (00-59)

Returns true if any one of the operands is true.

Returns true if both of the operands is true.

Returns true if the value given is false, and false if the value given is true..

Returns true.

Returns false.

Convert a number to a formatted string value.

By default, US formatting is applied; the optional culture parameter lets you specify the target format culture.

e.g. FORMAT-NUM({{numfield}}, ‘00.00’) outputs 4.9675 as: 04.97

e.g. FORMAT-NUM({{numfield}}, ‘00.00’, ‘fr-FR’)

outputs 4.9675 as: 04,97

Typical format specifies include:

0 – Replaces with digit or zero if none

# – Replaces with digit or nothing if none

. – Sets the decimal separator position

, – Sets grouping operator position

Converts the given value to a date value.

Converts the given value to a string value.

Converts the given value to an integer value.

Converts the given value to a numerical value.

Converts the given value to a boolean value.

Returns a Unicode checkbox that is ticked or crossed, depending on whether val equals matchTo.

e.g. CBOX({{myfield}}, ‘Yes’)

outputs ☑ or ☒

Returns a Unicode checkbox that is ticked or blank, depending on whether val equals matchTo.

e.g. CBOXB({{myfield}}, ‘Yes’)

outputs ☑ or ☐

Generates the web URL to the given image field.

Useful for assignment into a Data Source image column or for providing direct download links in Connector outputs.

Counts rows in the given Data Source, optionally filtering rows by formula. Refer to the Data Source by it’s External Id (from the Data Source -> Settings page)

e.g. DSCOUNT(‘STAFF’)

Add a filter formula using {{this[column]}} to refer to columns.

e.g. DSCOUNT(‘STAFF’, ‘{{this[2]}} = “BOB”‘)

counts rows where 3rd column = BOB

Sums column values in the given Data Source, optionally filtering rows by formula. Refer to the Data Source by it’s External Id (from the Data Source -> Settings page).

Refer to the column by it’s zero-based index.

e.g. DSSUM(‘STAFF’, 2)

sums the 3rd column’s values

Add a filter formula using {{this[column]}} to refer to columns.

e.g. DSSUM(‘STAFF’, 2, ‘{{this[5]}} = “BOB”‘)

sums 3rd column where 6th column = BOB

Averages column values in the given Data Source, optionally filtering rows by formula. Refer to the Data Source by its External Id (from the Data Source -> Settings page). Refer to the column by its zero-based index.

e.g. DSAVG(‘STAFF’, 2)

averages the 3rd column’s values

Add a filter formula using {{this[column]}} to refer to columns.

e.g. DSAVG(‘STAFF’, 2, ‘{{this[5]}} = “BOB”‘)

averages 3rd column where 6th column = BOB

Gets maximum column value in the given Data Source, optionally filtering rows by formula. Refer to the Data Source by it’s External Id (from the Data Source -> Settings page). Refer to the column by it’s zero-based index.

e.g. DSMAX(‘STAFF’, 2)

gets 3rd column’s max value

Add a filter formula using {{this[column]}} to refer to columns.

e.g. DSMAX(‘STAFF’, 2, ‘{{this[5]}} = “BOB”‘)

maxes 3rd column where 6th column = BOB

Gets minimum column value in the given Data Source, optionally filtering rows by formula. Refer to the Data Source by it’s External Id (from the Data Source -> Settings page). Refer to the column by it’s zero-based index.

e.g. DSMIN(‘STAFF’, 2)

gets 3rd column’s max value

Add a filter formula using {{this[column]}} to refer to columns.

e.g. DSMIN(‘STAFF’, 2, ‘{{this[5]}} = “BOB”‘)

mins 3rd column where 6th column = BOB

Gets first column value in the given Data Source, optionally filtering rows by formula. Refer to the Data Source by it’s External Id (from the Data Source -> Settings page). Refer to the column by it’s zero-based index.

e.g. DSFIRST(‘STAFF’, 2)

gets 3rd column’s first value

Add a filter formula using {{this[column]}} to refer to columns.

e.g. DSFIRST(‘STAFF’, 2, ‘{{this[5]}} = “BOB”‘)

first of 3rd column where 6th column = BOB

Gets last column value in the given Data Source, optionally filtering rows by formula. Refer to the Data Source by it’s External Id (from the Data Source -> Settings page). Refer to the column by it’s zero-based index.

e.g. DSLAST(‘STAFF’, 2)

gets 3rd column’s last value

Add a filter formula using {{this[column]}} to refer to columns.

e.g. DSLAST(‘STAFF’, 2, ‘{{this[5]}} = “BOB”‘)

last of 3rd column where 6th column = BOB

Collects answers from fields with data names matching the given regular expression pattern. List results are typically used with functions like SUM()/COUNT() to calculate results on fields that follow a common naming convention – e.g. survey fields like q1, q2, q3 etc.

Second optional parameter applies a filter formula to the answers gathered, leaving only answers that pass the condition. Use {{this}} to refer to the answer value in formula.

e.g. SUM(LIST(‘q[0-9]+’)) sums answers for fields named q1, q2 etc

e.g. COUNT(LIST(‘q[0-9]+’, ‘{{this}} = 5’)) counts q1,q2 etc fields where answers equal to 5

Converts the given value to a List. The value must be text containing delimited List elements – e.g. 34|76|9 Second optional parameter is the delimiter character separating elements. Default is pipe character.

Third optional parameter applies a filter formula to the List elements gathered, leaving only elements that pass the condition. Use {{this}} to refer to the element value in formula.

e.g. TOLIST({{myfield}})

e.g. TOLIST({{myfield}}, ‘STARTSWITH({{this}}, “B”)’)

e.g. TOLIST(‘3,6,9,62’, ‘,’, ‘{{this}} > 5’))

Returns true if the given value is found within the given List.

e.g. IN(‘ABC’, LIST(‘q[0-9]+’))

e.g. IN(‘ABC’, PRIOR(‘repeatfield’))

Returns true if the given value is NOT found within the given List.

e.g. NOTIN(‘ABC’, TOLIST({{listfield}}))

e.g. NOTIN(‘ABC’, PRIOR(‘repeatfield’))

Counts the values in the given List. Parameter must be a valid List function such as LIST() or PRIOR()

e.g. COUNT(LIST(‘q[0-9]+’))

Sums the values in the given numeric List. Parameter must be a valid List function such as LIST() or PRIOR()

e.g. SUM(LIST(‘q[0-9]+’))

Averages the values in the given numeric List. Parameter must be a valid List function such as LIST() or PRIOR()

e.g. AVERAGE(LIST(‘q[0-9]+’))

Gets minimum value in the given numeric List. Parameter must be a valid List function such as LIST() or PRIOR()

e.g. MIN(LIST(‘q[0-9]+’))

Gets the maximum value in the given numeric List. Parameter must be a valid List function such as LIST() or PRIOR()

e.g. MAX(LIST(‘q[0-9]+’))

Gets the first value in the given List. Parameter must be a valid List function such as LIST() or PRIOR()

e.g. FIRST(LIST(‘q[0-9]+’))

Gets the last value in the given List. Parameter must be a valid List function such as LIST() or PRIOR()

e.g. LAST(LIST(‘q[0-9]+’))

The page/row number of the current repeat Page or Table row.

Useful for generating incremental numbers for sections/clauses (e.g. 1.1, 1.2, 1.3)

Parameter is the data name of the repeatable page or table.

e.g. POSITION({{repeatpage}})

Gets the previous repeat/row value(s) of the named field within a Page/Table, starting from the current repeat context. Should only be used within a repeatable Page or Table. Optional second parameter specifies how many prior answers to retrieve. Prior repeat values are returned as a List unless the second parameter is 1; if so the value of the last prior answer is returned. Useful for performing aggregate functions on repeat values thus far (e.g. SUM,COUNT) and, if second parameter is 1, for copying forward the previous repeat/row value into the current new instance.

e.g. PRIOR(‘myfield’) List of all prior answers

e.g. PRIOR(‘myfield’, 1) last prior value only

Counts the repeats/rows of a Page/Table.

Useful for counting rows/repeats captured – e.g. an order line count

e.g. COUNT({{repeatPage}})

Sums a Number field across all repeats/rows of a Page/Table.

Useful for totalling values captured – e.g. an order line total

e.g. SUM({{numberfield}})

Averages a Number field across all repeats/rows of a Page/Table.

Useful for aggregating values captured – e.g. an average quantity

e.g. AVERAGE({{numberfield}})

e.g. SUM(LIST(‘q[0-9]+’))

e.g. AVERAGE(LIST(‘q[0-9]+’))

Maximum value of a Number field across all repeats/rows of a Page/Table.

e.g. MAX({{mynumberfield}})

Minimum value of a Number field across all repeats/rows of a Page/Table.

e.g. MIN({{mynumberfield}})

Value of the first occurrence/row of a repeatable Page or Table field.

e.g. FIRST({{myrepeatfield}})

Value of the last occurrence/row of a repeatable Page or Table field.

e.g. LAST({{myrepeatfield}})

Returns true if the value given is selected in the given choices field, false otherwise.

Returns the number of choices selected on the given choices field.

Returns the latitude in decimal degrees of the given location value.

Returns the longitude in decimal degrees of the given location value.

Returns the heading against true north in decimal degrees of the given location value.

Returns the altitude above/below sea level in metres of the given location value.

Returns the accuracy in metres of the given location value.

Returns the street number for the given location value.

Matches ‘sub_thoroughfare’ on OASIS Specification.

Returns the street name for the given location value.

Matches ‘thoroughfare’ on OASIS Specification.

Returns the city / locality name for the given location value.

Matches ‘locality’ on OASIS Specification.

Returns the county / district area for the given location value. Mainly USA specific.

Matches ‘sub_admin_area’ on OASIS Specification

Returns the state / province for the given location value.

Matches ‘admin_area’ on OASIS Specification.

Returns the postal / zip code of the given location value.

Matches ‘postal_code’ on OASIS Specification.

Returns the ISO country code of the given location value.

Matches ‘country’ on OASIS Specification.

Finds the miles between two geo-points, using great-circle math.

Geo-points are strings in ‘lat lon’ format, Location field answers are also geo-points.

e.g. MIBETWEEN(‘-8.45234 27.7623423’, {{myGpsField}})

Finds the kilometres between two geo-points, using great-circle math.

Geo-points are strings in ‘lat lon’ format, Location field answers are also geo-points.

e.g. KMBETWEEN(‘-8.45234 27.7623423’, {{myGpsField}})

Returns a true/false answer on whether the given geo-point is within the given polygon.

Polygon values must be pipe-seperated string of geo-points.

e.g. INPOLYGON({{myGpsVal}}, ‘-8.6782523 27.2918257|-8.6672229 28.7094422|-7.6447228 29.3849982’)

Returns the name of the current Step in the process. If no Step has occurred yet, then this function returns a blank value.

Returns true if the named Process Step field is the current Step in the process. If no Step has occurred yet, this function returns true for ANY Process Step name.

Returns the result of the named Process Step field, if any is set.

Returns the email of the user that decided the result (if any) of the named Process Step field.

Returns the first name of the user that decided the result (if any) of the named Process Step field.

Returns the last name of the user that decided the result (if any) of the named Process Step field.

Returns the number 3.14159265358979, the mathematical constant Pi, accurate to 15 digits.

Converts radians to degrees.

Converts degrees to radians.

Returns a number which, when multiplied by it self, will produce the given value.

Returns the exponent to which the given base must be raised to produce the given value.

Returns sine of given radian angle value.

Returns cosine of given radian angle value.

Returns tangent of given radian angle value.

Returns the arcsine, or inverse sine, of a number.

The returned angle is given in radians in the range -pi/2 to pi/2.

Returns the arccosine, or inverse cosine, of a number.

The returned angle is given in radians in the range 0 (zero) to pi.

Returns the arctangent, or inverse tangent, of a number.

The returned angle is given in radians in the range -pi/2 to pi/2.

Returns hyperbolic sine of given radian angle value.

Returns hyperbolic cosine of given radian angle value.

Returns hyperbolic tangent of given radian angle value.

Formula builder cheat sheet now available on the Knowledge Base.