Function arguments in square brackets ([ ... ]) are optional. Note: when substituting Community Connect 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 weekYEAR(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.
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.
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.
MAX(arg1, arg2, ...)
Returns the maximum value of all arguments, e.g.
Please complete at most @(MAX(flow.questions, 10)) questionsMIN(arg1, arg2, ...)
Returns the minimum value of all arguments, e.g.
Please complete at least @(MIN(flow.questions, 10)) questionsPOWER(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.6160317611RANDBETWEEN(bottom, top)
Returns a random integer number between the numbers you specify.
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)
@(ROUNDUP(9.4378, 2))
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 formsTRUNC(number)
Truncates a number to an integer by removing the fractional part of the number.
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 accountINT(number)
Rounds a number down to the nearest integer.
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)) charactersLOWER(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.
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))!!Community Connect Functions
These Community Connect-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)) reportsREAD_DIGITS(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)) wordsWORD_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("CommunityConnect expressions are fun", 2, 4)) will return 2nd and 3rd words "expressions are"
@(WORD_SLICE("CommunityConnect expressions are fun", 2)) will return "expressions are fun"
@(WORD_SLICE("CommunityConnect expressions are fun", 1, -2)) will return "CommunityConnect expressions"
@(WORD_SLICE("CommunityConnect 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 Community Connect where it begins and ends, e.g.
@(SUM(contact.reports, step.value)) * 2 <-- the "* 2" isn't evaluatedshould be written as
@(SUM(contact.reports, step.value) * 2)