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 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.

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)) 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.

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 forms

TRUNC(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 account

INT(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)) 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.

PROPER(text)

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

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)) reports

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

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("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 evaluated

should be written as

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