Function Reference

Function arguments in square brackets (`[ ... ]`) are optional. Note: when substituting variables, e.g. @(TIMEVALUE(flow.12_hour_time.category)), quotes aren't necessary.

Date & Time Functions

DATE(year, month, day)

Defines a new date value, e.g.

``This is a date @(DATE(2012, 12, 25))``

DATEVALUE
(text)

Converts date stored in text to an actual date, using your organization's date format setting, e.g.

``You joined on @(DATEVALUE(contact.joined_date))``

DAY
(date)

Returns only the day of the month of a date (1 to 31), e.g.

``The current day is@(DAY(contact.joined_date))``

DAYS
("end_date", "start_date")

Returns the number of days between two dates.

``@(DAYS("02-28-2016", "02-28-2015"))``

DATEDIF
("start_date", "end_date", "units")

Calculates the number of days, months or years between two dates. Units are abbreviated "D", "M", and "Y".

``@(DATEDIF("02-26-2015", "02-26-2016", "M"))``

EDATE
(date, months)

Moves a date by the given number of months, e.g.

``Next month's meeting will be on @(EDATE(date.today, 1))``

HOUR
(datetime)

Returns only the hour of a datetime (0 to 23)

``The current hour is @(HOUR(NOW()))``

MINUTE
(datetime)

Returns only the minute of a datetime (0 to 59), e.g.

``The current minute is @(MINUTE(NOW()))``

MONTH
(date)

Returns only the month of a date (1 to 12), e.g.

``The current month is @(MONTH(NOW()))``

NOW
()

Returns the current date and time, e.g.

``It is currently @(NOW())``

SECOND
(datetime)

Returns only the second of a datetime (0 to 59), e.g.

``The current second is @(SECOND(NOW()))``

TIME
(hours, minutes, seconds)

Defines a time value which can be used for time arithmetic, e.g.

``2 hours and 30 minutes from now is @((date.now + TIME(2, 30, 0)))``

TIMEVALUE
(text)

Converts time stored in text to an actual time, e.g.

``Your appointment is at @((date.today + TIMEVALUE("2:30")))``

TODAY
()

Returns the current date, e.g.

``Today's date is @(TODAY())``

WEEKDAY
(date)

Returns the day of the week of a date (1 for Sunday to 7 for Saturday), e.g.

``Today is day no. @(WEEKDAY(TODAY())) in the week``

YEAR
(date)

Returns only the year of a date, e.g.

``The current year is @(YEAR(NOW()))``

Logical Functions

AND(arg1, arg2, ...)

Returns TRUE if and only if all its arguments evaluate to TRUE, e.g.

``@(AND(contact.gender = "F", contact.age >= 18))``

FALSE
()

Returns the logical value false.

`@(FALSE(contact.gender = "F"))`

IF
(arg1, arg2, ...)

Returns one value if the condition evaluates to TRUE, and another value if it evaluates to FALSE, e.g.

``Dear @(IF(contact.gender = "M", "Sir", "Madam"))``

OR
(arg1, arg2, ...)

Returns TRUE if any argument is TRUE, e.g.

``@(OR(contact.state = "GA", contact.state = "WA", contact.state = "IN"))``

TRUE
()

Returns the logical value true.

`@(TRUE(contact.gender = "M"))`

Math Functions

ABS(number)

Returns the absolute value of a number, e.g.

``The absolute value of -1 is @(ABS(-1))``

AVERAGE
(number, ...)

Returns the average (arithmetic mean) of the arguments.

`On average your mood was @(AVERAGE(flow.mood_day_1, flow.mood_day_2)).`

MAX
(arg1, arg2, ...)

Returns the maximum value of all arguments, e.g.

``Please complete at most @(MAX(flow.questions, 10)) questions``

MIN(arg1, arg2, ...)

Returns the minimum value of all arguments, e.g.

``Please complete at least @(MIN(flow.questions, 10)) questions``

POWER
(number, power)

Returns the result of a number raised to a power - equivalent to the `^` operator, e.g.

``2 to the power of 3 is @(POWER(2, 3))``

RAND
()

Returns an evenly-distributed random real number greater than or equal to 0 and less than 1, e.g.

``0.6160317611``

RANDBETWEEN
(bottom, top)

Returns a random integer number between the numbers you specify.

`@(RANDBETWEEN(123, 456))`

ROUND
("number", number of digits)

Rounds a number to a specified number of digits.

``@(ROUND(9.4378, 3))``

ROUNDDOWN
(number, number of digits)

Rounds a number down towards zero to the specified number of digits.

``@(ROUNDDOWN(9.4378, 3))``

ROUNDUP
(number, number of digits)

Rounds a number up towards zero to the specified number of digits.

``@(ROUNDUP(9.4378, 2))``

SUM
(arg1, arg2, ...)

Returns the sum of all arguments, equivalent to the `+` operator, e.g.

``You have @(SUM(contact.reports, contact.forms)) reports and forms``

TRUNC
(number)

Truncates a number to an integer by removing the fractional part of the number.

`@(TRUNC(123.45))`

Text Functions

CHAR(number)

Returns the character specified by a number, e.g.

``As easy as @(CHAR(65)), @(CHAR(66)), @(CHAR(67))``

CLEAN
(text)

Removes all non-printable characters from a text string, e.g.

``You entered @(CLEAN(step.value))``

CODE
(text)

Returns a numeric code for the first character in a text string, e.g.

``The numeric code of A is @(CODE("A"))``

CONCATENATE
(args)

Joins text strings into one text string, e.g.

``Your name is @(CONCATENATE(contact.first_name, " ", contact.last_name))``

FIXED
(number, [decimals], [no_commas])

Formats the given number in decimal format using a period and commas

``You have @(FIXED(contact.balance, 2)) in your account``

INT
(number)

Rounds a number down to the nearest integer.

`@(INT(123))`

LEFT(text, num_chars)

Returns the first characters in a text string, e.g.

``You entered PIN @(LEFT(step.value, 4))``

LEN
(text)

Returns the number of characters in a text string, e.g.

``You entered @(LEN(step.value)) characters``

LOWER
(text)

Converts a text string to lowercase, e.g.

``Welcome @(LOWER(contact))``

MOD
(number, divisor)

Returns the remainder after a number is divided by the divisor.

`@(MOD(123, 4))`

PROPER
(text)

Capitalizes the first letter of every word in a text string, e.g.

``Your name is @(PROPER(contact))``

REPT
(text, number_times)

Repeats text a given number of times, e.g.

``Stars! @(REPT("*", 10))``

RIGHT
(text, num_chars)

Returns the last characters in a text string, e.g.

``Your input ended with ... @(RIGHT(step.value, 3))``

SUBSTITUTE
(text, old_text, new_text, [instance_num])

Substitutes new_text for old_text in a text string. If `instance_num` is given, then only that instance will be substituted, e.g.

``@(SUBSTITUTE(step.value, "can't", "can"))``

UNICHAR
(number)

Returns the unicode character specified by a number, e.g.

``As easy as @(UNICHAR(65)), @(UNICHAR(66)), @(UNICHAR(67))``

UNICODE
(text)

Returns a numeric code for the first character in a text string, e.g.

``The numeric code of A is @(UNICODE("A"))``

UPPER
(text)

Converts a text string to uppercase, e.g.

``WELCOME @(UPPER(contact))!!``

TextIt Functions

These platform-specific functions are not found in Excel but have been provided for the sake of convenience.

FIELD(text, index, [delimiter])

Reference a field in a string separated by a delimiter.

``@FIELD("hello world", 2, " ")``

FIRST_WORD
(text)

Returns the first word in the given text - equivalent to `WORD(text, 1)`, e.g.

``The first word you entered was @(FIRST_WORD(step.value))``

PERCENT(number)

Formats a number as a percentage, e.g.

`You've completed @(PERCENT(contact.reports_done / 10)) reports `

(text)

Formats digits in text for reading in TTS, e.g.

``Your number is @(READ_DIGITS(contact.tel_e164))``

REMOVE_FIRST_WORD
(text)

Removes the first word from the given text. The remaining text will be unchanged e.g.

``You entered @(REMOVE_FIRST_WORD(step.value))``

WORD
(text, number, [by_spaces])

Extracts the nth word from the given text string. If `stop` is a negative number, then it is treated as count backwards from the end of the text. If `by_spaces` is specified and is `TRUE` then the function splits the text into words only by spaces. Otherwise the text is split by punctuation characters as well, e.g.

``@(WORD("hello cow-boy", 2)) will return "cow"@(WORD("hello cow-boy", 2, TRUE)) will return "cow-boy"@(WORD("hello cow-boy", -1)) will return "boy"``

WORD_COUNT
(text, [by_spaces])

Returns the number of words in the given text string. If `by_spaces` is specified and is `TRUE` then the function splits the text into words only by spaces. Otherwise the text is split by punctuation characters as well, e.g.

``You entered @(WORD_COUNT(step.value)) words``

WORD_SLICE
(text, start, [stop], [by_spaces])

Extracts a substring of the words beginning at `start`, and up to but not-including `stop`. If `stop` is omitted then the substring will be all words from `start` until the end of the text. If `stop` is a negative number, then it is treated as count backwards from the end of the text. If `by_spaces` is specified and is `TRUE` then the function splits the text into words only by spaces. Otherwise the text is split by punctuation characters as well, e.g.

``@(WORD_SLICE("TextIt expressions are fun", 2, 4)) will return 2nd and 3rd words "expressions are"@(WORD_SLICE("TextIt expressions are fun", 2)) will return "expressions are fun"@(WORD_SLICE("TextIt expressions are fun", 1, -2)) will return "TextIt expressions"@(WORD_SLICE("TextIt expressions are fun", -1)) will return "fun"``

Troubleshooting

Why is only part of my expression evaluated?

If the expression is not a single variable or function call, then you must enclose it in parentheses to tell the platform where it begins and ends, e.g.

``@(SUM(contact.reports, step.value)) * 2  <-- the "* 2" isn't evaluated``

should be written as

``@(SUM(contact.reports, step.value) * 2)``