Expressions | Smart Flows

An expression is a combination of functions, fields and values that produces another output. They are very useful for calculations, textual changes and logical operations.

Concept

Typically, you use expressions for one of the following purposes:

Defining values that aren't present in your data set.
Manipulating existing values so that the output is different.
Creating more complex conditions than the wizard allows.

Defining values

The simplest way to use an expression is to define a value that is not present in your data set. For instance, here we define the string Mr:

This will simply output the string Mr.

On its own, defining a value isn't that useful. In combination with functions, however, it becomes more powerful. For instance, here we check whether the type field's value is equal to the string black marker:

This will return a value that can then be checked in a condition.

Manipulating existing values

Changing an existing value so that it outputs something different is done by using functions. For instance, here's a table that shows the effect of a few functions on some fields' values:

Value of field A: 3
Value of field B: 6
Value of field C: 'Experlogix'
Value of field D: ' John'

Function	Description	Input fields	Outcome
sum	Returns the sum of the fields.	field A & B	9
max	Returns the largest number.	field A & B	6
toUpper	Makes all characters uppercase.	field C	EXPERLOGIX DOCUMENT AUTOMATION
concat	Puts two strings together.	field C & D	XperiDo John

This page shows all of the available functions.

Complex conditions

Instead of using the wizard to define your conditions, you may also use expressions. We always encourage you to use the wizard as much as possible, but there are a few things the wizard won't let you do. For instance, you can't insert multiple conditions in a condition through the wizard.

There are a few functions that return 1 (true) or 0 (false); these are called logical functions. The eq function (to check whether a value is equal to another value) is a good example:

This expression will return 1 if the type field's value is equal to black marker. This expression can also be defined through the wizard:

But more complex conditions require expressions. Here is an example of a condition that checks whether the type field's value is equal to either black marker or red marker:

Here, we use the or function that checks whether one of the arguments is true (which means that it returns 1). So, if either of the eq functions is true, the entire expression is true and the condition is thus true.

Elements of an expression

An expression can contain the following elements:

Functions
Fields
Numbers
Strings

Let's go over each of them.

Functions

Functions are shown in pink (although they may appear in black the second time you open the expression):

Adding a function to an expression can be done in 2 ways: either you type it in or you use the function wizard.

To use the function wizard, follow these steps:

Select the Insert Function button:
A window will pop up, asking you which function you want to include.
Choose your function (you can search for it or browse by category) and select OK. This opens a new window where you can choose the function's arguments:
You can type in your argument, use the expression button to bring up another expression editor (which you can then use for inserting fields if you like), or you can select the green plus to add another argument (if the functions allows a variable number of arguments). Select the red cross to remove an argument.
Select OK and your function, with all the arguments you provided, will be inserted into the expression:

Tip The faster way is just to type avg(,3) and insert your field before the comma. But the wizard is helpful when you're not used to working with expressions.

Fields

Fields are shown with ${} around them:

To insert a field, follow these steps:

Set the mouse cursor where you want to insert the field.
Select the Insert Field button, select your field and select OK.

The field is now inserted into the expression.

This expression will now output the value of the field.

Numbers

Numbers are shown in brown (when used in a function):

To insert a number, simply type it. To use a decimal separator, use a dot, like this:

Strings

Strings are shown in green:

Strings will only be recognized as such when they are put in between apostrophes. Otherwise, the text won't be green and thus it won't be a valid string.

Pretty print

The expression window features a Pretty print button.

Clicking it will tidy up your expression according to various formatting rules, such as added indentation, parameters on a separate line etc. Here's an example:

The sum of two fields and 50000:

It is easier to discern the three arguments of the sum() function in the prettyprinted version.

Another example:

Checking whether the following expression is true: is the type a pencil or a marker with the description red?

Pretty cluttered without pretty print.

As you see, for multi-function expressions, pretty print is a great feature as you get a better view of the whole expression. It is especially useful for chaining conditions.

Chaining conditions

A more advanced use of expressions is to chain conditions together to create a condition chain. This makes it possible to evaluate many conditions one after another, until one is true. You can use this to check for multiple things at the same time.

For instance: you want to change a number field to text, as follows: 1 is Monday, 2 is Tuesday, 3 is Wednesday etc. So when your field contains the number 4, you want Thursday to appear. You could do this with the Text replace feature, but using an expression for this has the advantage that you can easily copy and adjust it if necessary.

Here's how we've set up the condition, using the warehouse field:

Check whether the warehouse field equals 1. If so, output Monday.
If not 1, check whether the warehouse field equals 2. If so, output Tuesday.
If not 1 or 2, check whether the warehouse field equals 3. If so, output Wednesday.
etc

We'll use the if(cond,a,b) function for this, which returns a if cond is true and b if cond is false. Also eq(a,b) to check whether a is equal to b. So basically, it'll look something like this: if (eq(warehouse,1), monday, if (eq(warehouse,2), tuesday, if (eq(warehouse,3), wednesday...

Here's the result, after pretty printing:

We added 'invalid day' at the end for when the input is not 1, 2, 3, 4, 5, 6 or 7.

Good to know

A few things that are good to know when using expressions:

A function that takes multiple arguments as its input will work with only one argument when that argument is recurring. For instance, the sum function can be used to return the sum of all the quantity fields, like below
This is often used in quotes and invoices, where each line item has its own subtotal. You can then just use the sum function on the subtotal field to find the grand total.
You can use the Validate button to check whether you made syntax errors. It's always a good idea to select Validate before clicking OK.

Your expression may be valid syntactically, but make no sense whatsoever in the context of your template. Use your best judgment.
When using pseudo-fields, be sure to select the right type. Some functions won't work on text and some won't work on numbers.
0 will always be considered false, but everything else is true. So if your condition returns 2, or 5.4, it will be considered true.

List of Experlogix Template Builder functions

The Experlogix Template Builder offers a large number of functions that you can use in your expressions. Use the expression tables below to power your Templates with additional functionality via expressions.

Regular expressions

Some of the functions you can use allow you to use regular expressions. To learn more about regular expressions, refer to Java Tutorials.

Extended characters

Some of the functions you can use allow you to use extended characters. To learn more about extended characters, refer to Control character.

Basic Functions

Characters	Description
\n	Newline
\r	Carriage return
\t	Tab
\\	Backslash
\x##	Hexadecimal value (between 00 and FF)
\u####	Extended hexadecimal value meant for Unicode characters (between 0000 and FFFF)
\d###	Decimal value (between 000 and 255)

Mathematical functions

Elementary arithmetic

Function name	Description	Syntax	Example
`add`	Returns the sum of c and b (c + b).	add(c, b)	add('1.2', '2.3') = 3.5 add('1.2', 2.3) = 3.5 add(1.2, 2.3) = 3.5
`uadd`	Returns the sum of c and 1 (c + 1).	uadd(c)	uadd(10) = 11
`sub`	Returns the result of subtracting b from c (c - b).	sub(c, b)	sub(9,3) = 6 sub(3,9) = -6
`div`	Returns the result of dividing c by b (c / b).	div(c, b)	div(9,2) = 4.5 div(-9,2) = -4.5 div(2,4) = 0.5
`mod`	Returns the remainder of the division of c and b.	mod(c, b)	mod(9,2) = 1 mod(9,3) = 0 mod(8,3) = 2 mod(-9,-2) = -1
`mult`	Returns the multiplication of c with b (c × b).	mult(c, b)	mult(2,6.5) = 13 mult(-2,8) = -16
`sum`	Returns the sum of all arguments (a + b + c + d...).	sum(a, b, c, d...)	sum(1,6.5,2) = 9.5 sum(1,0,0,0,6) = 7
`prod`	Returns the product of all arguments (a x b x c x d...).	prod(a, b, c, d...)	prod(2,3,4) = 24 prod(-2,1,-3.5,2) = 14

Basic

Function Name	Description	Synta	Example
`md`	Returns a new random number between 0.0 and 1.0. Seed is the number used to initialize a pseudo-random number generator. The seed is not required.	rnd(seed)	rnd(25) = 0.645
`abs`	Returns the absolute value of c (\|c\|).	abs(c)	abs(123) = 123 abs(-123) = 123
`max`	Returns the largest number of c and b.	max(c, b)	max(5,2) = 5 max(-3,7) = 7 max(-3,-5) = -3
`min`	Returns the smallest number of c and b.	min(c,b)	min(5,2) = 2 min(-3,7) = -3 min(-3,-5) = -5
`negate`	Returns c multiplied with -1.	negate(c)	negate(63) = -63 negate(-63) = 63
`sign`	Returns -1 if c is negative, 1 if c is positive, and 0 if c is 0.	sign(c)	sign(0) = 0 sign(-4) = -1 sign(7) = 1
`val`	Converts c to a floating point value.	val(c)	val(1.2) = 1.2

Power

Function Name	Description	Syntax	Example
`sqrt`	Returns the square root of c.	sqrt(c)	sqrt(121) = 11 sqrt(499849) = 707
`sqr`	Returns the square of c (c²).	sqr(c)	sqr(11) = 121 sqr(707) = 499849
`exp`	Returns e raised to the power c.	exp(c)	exp(2) = 7.389
`pow`	Returns c raised to the power b (c ^ b).	pow(c, b)	pow(10,3) = 1000 pow(2,8) = 256 pow(3,3) = 27

Logarithms

Function Name	Description	Syntax	Example
`ln`	Returns the natural logarithm of c (base e).	ln(c)	ln(10) = 2.303
`log`	Returns the logarithm of c in base 10.	log(c)	log(1000) = 3 log(100) = 2
`log2`	Returns the logarithm of c in base 2.	log(c)	log2(1024) = 10 log2(256) = 8
`logn`	Returns the logarithm of c in base n.	logn(c, n)	logn(343,7) = 3 logn(64,4) = 3 logn(100,10) = 2

Rounding

Function Name	Description	Syntax	Example
`ceil`	Returns the smallest integer higher than or equal to c (rounding up).	ceil(c)	ceil(1.0) = 1 ceil(1.1) = 2 ceil(1.8) = 2 ceil(-1.0) = -1 ceil(-1.1) = -1
`floor`	Returns the largest integer lower than or equal to c (rounding down).	floor(c)	floor(1.0) = 1 floor(1.1) = 1 floor(2.5) = 2 floor(-1.0) = -1 floor(-1.3) = -2
`trunc`	Returns c without the decimal part.	trunc(c)	trunc(1.0) = 1 trunc(1.6) = 1 trunc(2.5) = 2 trunc(-3.6) = -3
`round`	Returns c rounded to the nearest integer.	round(c)	round(1.0) = 1 round(1.2) = 1 round(1.5) = 1 round(1.7) = 2
`roundDecimal`	Returns c rounded to a number of decimal places (b).	roundDecimal(c,b)	round(2.12355,3) = 2.124 round(2.12555,3) = 2.126 round(2.12555,2) = 2.13 round(2.12555,1) = 2.1

Trigonometrical

Function Name	Description	Syntax	Example
`sin`	Returns the sine of c (c in degrees).	sin(c)	sin(0) = 0 sin(30) = 0.5 sin(45) = 0.707 sin(90) = 1
`cos`	Returns the cosine of c (c in degrees).	cos(c)	cos(0) = 1 cos(45) = 0.707 cos(60) = 0.5 cos(90) = 0
`tan`	Returns the tangent of c. (c in degrees).	tan(c)	tan(0) = 0 tan(45) = 1 tan(60) = 1.732

Function Name

Description

Syntax

Example

sin

Returns the sine of c (c in degrees).

sin(c)

sin(0) = 0

sin(30) = 0.5

sin(45) = 0.707

sin(90) = 1

cos

Returns the cosine of c (c in degrees).

cos(c)

cos(0) = 1

cos(45) = 0.707

cos(60) = 0.5

cos(90) = 0

tan

Returns the tangent of c. (c in degrees).

tan(c)

tan(0) = 0

tan(45) = 1

tan(60) = 1.732

Counting

Function Name	Description	Syntax	Example
`avg`	Returns the average of all the arguments. Blank values are not included.	avg(a,b,c...)
`avga`	Returns the average of all the arguments. Blank values are included and considered to be zero.	avga(a,b,c...)
`median`	Returns the median of all the arguments. Blank values are not included.	median(a,b,c...)	median(1,5,7) = 5 median(3,6,18) = 6 median(3,6,8,11) = 7
`mediana`	Returns the median of all the arguments except x. Blank values get the value x.	mediana(x,a,b,c...)
`mode`	Returns the number that most often appears in the arguments. Blank values are not included.	mode(a,b,c...)	mode(1,2,2,3) = 2 mode(1,2,2,3,3) = no mode mode(1,2,2,3,3,3) = 3
`modea`	Returns the number that most often appears in the arguments except x. Blank values get the value x.	modea(x,a,b,c...)
`count`	Counts the number of values. Blank values are not included.	count(a,b,c...)
`counta`	Counts the number of values. Blank values are included.	counta(a,b,c...)
`countb`	Counts the number of blank values.	countb(a,b,c...)
`countDistinctValues`	Counts the number of distinct values. Blank values are not included.	countDistinctValues(a,b,c...)	countDistinctValues(a,a,a,b,b) = 2 countDistinctValues(a,b,c,c,c,c) = 3
`countDistinctValuesA`	Counts the number of distinct values. Blank values are included.	countDistinctValues(a,b,c...)

Text functions

Basic

Function Name	Description	Syntax	Example
str	Converts c to a string.	str(c)	str(1.2) = 1.2
strlen	Returns the length of c.	strlen(c)	strlen('Hello world!') = 12 strlen('Xpertdoc') = 8
concat	Returns the string that results from concatenating c and b. You can use more than two arguments.	concat(c, b)	concat('Hello ', 'world') = 'Hello world' concat('Hi ','there ','everyone') = 'Hi there everyone'
substr	Returns the sub-string of s starting from c with length b.	substr(s, c, b)	substr('Hello World!', 1, 4) = 'ello' substr('Hello World!', 3, 2) = 'lo' substr('Hello World!', 3, -2) = 'el' substr('Hello World!', 100, -4) = 'rld!'
chr	Returns the nth character of c.	chr(c, n)	chr('Xpertdoc', 2) = 'p' chr('Xpertdoc', 150) = ''

Trim

Function Name	Description	Syntax	Example
trim	Removes all leading and trailing whitespaces from c.	trim(c)	trim(' some text ') = 'some text'
rtrim	Removes all trailing whitespaces from c.	rtrim(c)	rtrim(' some text ') = ' some text'
ltrim	Removes all leading whitespaces from c.	ltrim(c)	ltrim(' some text ') = 'some text '

Case

Function Name	Description	Syntax	Example
toLower / lowercase	Returns c with all characters in lowercase.	toLower(c) lowercase(c)	toLower('Xpertdoc') = 'xpertdoc' lowercase('Xpertdoc') = 'xpertdoc'
toUpper / uppercase	Returns c with all characters in uppercase.	toUpper(c) uppercase(c)	toUpper('Xpertdoc') = 'XPERTDOC' uppercase('Xpertdoc') = 'XPERTDOC'
capitalizeEachWord	Returns c where each word has a capital first letter.	capitalizeEachWord(c)	capitalizeEachWord('this is very good.') = 'This Is Very Good.'
sentenceCase	Returns c where each sentence is capitalized.	sentenceCase(c)	sentenceCase('hi there. we are going to start.') = 'Hi there. We are going to start.'
toggleCase	Returns c where each letter's case is inverted.	toggleCase(c)	toggleCase('This is a strange function according to Dr. Strangefunction.') = 'tHIS IS A STRANGE FUNCTION ACCORDING TO dR. sTRANGEFUNCTION.'

Replace

Function Name	Description	Syntax	Example
replaceText	Returns a string where each occurrence of d in c has been replaced with e. Case is ignored.	replaceText(c, d, e)	replaceText('The quick brown fox jumps over the lazy dog', 'brown', 'orange') = 'The quick orange fox jumps over the lazy dog' replaceText('The quick brown fox jumps over the lazy dog', 'BROWN', 'ORANGE') = 'The quick ORANGE fox jumps over the lazy dog' replaceText('The quick BROWN fox jumps over the lazy dog', 'brown', 'orange') = 'The quick orange fox jumps over the lazy dog' replaceText('The quick brown fox jumps over the lazy dog', 'brown', '') = 'The quick fox jumps over the lazy dog'
replaceTextC	Returns a string where each occurrence of d in c has been replaced with e. Case-sensitive.	replaceTextC(c, d, e)	replaceTextC('The quick brown fox jumps over the lazy dog', 'brown', 'orange') = 'The quick orange fox jumps over the lazy dog' replaceTextC('The quick brown fox jumps over the lazy dog', 'BROWN', 'ORANGE') = 'The quick brown fox jumps over the lazy dog' replaceTextC('The quick BROWN fox jumps over the lazy dog', 'brown', 'orange') = 'The quick BROWN fox jumps over the lazy dog' replaceTextC('The quick brown fox jumps over the lazy dog', 'brown', '') = 'The quick fox jumps over the lazy dog'
replaceExtended	Returns a string where each occurrence of d in c has been replaced with e. d and e are considered to be texts with extended characters. Case is ignored.	replaceExtended(c, d, e)	replaceExtended('"??"', '\u0100', 'Latin capital letter A with macron') = '"Latin capital letter A with macron"'
replaceExtendedC	Returns a string where each occurrence of d in c has been replaced with e. d and e are considered to be texts with extended characters. Case-sensitive.	replaceExtendedC(c, d, e)	replaceExtendedC('"??"', '\u0100', 'Latin capital letter A with macron') = '"Latin capital letter A with macron"'
replaceRegEx	Returns a string where each occurrence of d in c has been replaced with e. d and e are considered to be regular expressions.	replaceRegEx(c, d, e)	replaceRegEx('The quick brown fox jumps over the lazy dog', '(dog\|fox)', '\[animal\]$1\[animal\]') = 'The quick brown [animal]fox[animal] jumps over the lazy [animal]dog[animal]'

Split

If you use any of the split functions, you need to configure it using the following steps:

Create a pseudo-field where you use the split function on a field.
Loop over the pseudo-field in your template.
In the loop, map the pseudo-field again. If your field is called Address1, this is what you'll end up with:

If you do not loop, you'll only get the first item instead of all the items.

Function Name

Description

Example

split

Splits a field into separate parts, which can be stored as an array in a pseudo-field. These can then be accessed by mapping the pseudo-field split(text, separator, isRegex, removeEmpty, trimParts).

Split Type	Description
text	The text to be split into pieces (text).
separator	The value to split on (text).
isRegex	Whether separator is a regular expression or not (1 = true, 0 = false).
removeEmpty	Whether to remove empty pieces from the result (1 = true, 0 = false).
trimParts	Whether to trim the pieces (1 = true, 0 = false).

split on lines split(${./Content}, '\\\\r?\\\\n', 1, 1, 1)—Splits the value of element <Content> into pieces separated by the regular expression \r?\n. Empty parts are removed and the separate parts are trimmed. The reason that you need to place all the backslashes here is that the regular expression is translated two times before it is fed to the regular expression matcher. The expression is translated to a java-string before it is evaluated; \\\\r -> \\r -> \r.
split on ',' or ';' split(${./Content}, '[,]|[;]', 1, 0, 0) Splits the value of element <Content> into pieces separated by the regular expression [,]|[;]. This means either a comma or a semi-colon. Empty parts are kept and the separate parts are not trimmed.
split on space split(${./Content}, ' ', 0, 0, 0)—Splits the value of element <Content> into pieces separated by one space. Empty parts are kept and the separate parts are not trimmed.

splitLines

Splits a field into separate parts, splitting on newlines splitLines(text). This function is equivalent to split(text, '\\r?\\n', 1, 0, 0) and will split text into pieces separated by a newline.

splitWords

Splits a field into separate parts, splitting on whitespaces splitWords(text). This function is equivalent to split(text, '\\\\s+', 1, 1, 1) and will split text into pieces separated by whitespaces.

Format

Function Name	Description	Syntax	Example
formatNumber	Output a number in a given pattern. For more information, refer to Number patterns.	formatNumber(number,pattern)	formatNumber(500,'0.00') = 500.00 formatNumber(200,'##.##') = 200
formatNumberR	Output a number in a given pattern, using a certain locale. For more information, see Number patterns and List of supported locales.	formatNumber(number,pattern,locale)	formatNumberR(500,'¤0.00','en_us') = $500.00 formatNumberR(500,'¤0.00','nl_be') = €500,00

Function Name

Description

Syntax

Example

formatNumber

Output a number in a given pattern. For more information, refer to Number patterns.

formatNumber(number,pattern)

formatNumber(500,'0.00') = 500.00

formatNumber(200,'##.##') = 200

formatNumberR

Output a number in a given pattern, using a certain locale. For more information, see Number patterns and List of supported locales.

formatNumber(number,pattern,locale)

formatNumberR(500,'¤0.00','en_us') = $500.00

formatNumberR(500,'¤0.00','nl_be') = €500,00

Date & time functions

For details on Date & time functions, please refer to the following pages

Logical functions

Logic

Function Name	Description	Syntax	Example
if	Returns d if c equates to true, e if c equates to false.	if(c, d, e)	if(1, yes, no) = yes if(0, yes, no) = no if(2, yes, no) = yes if(-1, yes, no) = yes
and	Returns the logical AND of c and d.	and(c, d)	and(1, 1) = 1 and(1, 0) = 0 and(0, 1) = 0 and(0, 0) = 0
or	Returns the logical OR of c and d.	or(c, d)	or(1, 1) = 1 or(1, 0) = 1 or(0, 1) = 1 or(0, 0) = 0
not	Returns 1 if c is 0, 0 otherwise.	not(c)	not(0) = 1 not(1) = 0 not(2) = 0 not(-1) = 0

Comparison

Function Name

Description

Syntax

Example

Returns 1 if c is equal to d, 0 otherwise. When used with text, the values are trimmed (trailing and leading whitespaces are removed), and case is ignored.

eq(c, d)

eq(2, 2) = 1

eq(2, 5) = 0

eq(2.0, 2) = 0

eq(' AbC ','abc') = 1

neq

Returns 1 if c is not equal to d, 0 otherwise. When used with text, the values are trimmed (trailing and leading whitespaces are removed), and case is ignored.

neq(c, d)

eq(2, 2) = 0

eq(2, 5) = 1

eq(2.0, 2) = 1

eq(' AbC ','abc') = 0

neq

Extensions of eq and neq. The eq and neq functions can be extended for use with text:

Function Name	Description
eqc	The same as eq, but case-sensitive.
eqt	The same as eq, but the values are not trimmed.
eqct	The same as eq, but case-sensitive and the values are not trimmed.
neqc	The same as neq, but case-sensitive.
neqt	The same as neq, but the values are not trimmed.
neqct	The same as neq, but case-sensitive and the values are not trimmed.

Returns 1 if c is bigger than d, 0 otherwise.

gt(c, d)

gt(2, 1) = 1

gt(2, 5) = 0

gt(2, 2) = 0

geq

Returns 1 if c is bigger than or equal to d, 0 otherwise.

geq(c, d)

geq(2, 1) = 1

geq(2, 5) = 0

geq(2, 2) = 1

Returns 1 if c is lower than d, 0 otherwise.

lt(c, d)

lt(2, 1) = 0

lt(2, 5) = 1

lt(2, 2) = 0

leq

Returns 1 if c is lower than or equal to d, 0 otherwise.

leq(c, d)

leq(2, 1) = 0

leq(2, 5) = 1

leq(2, 2) = 1

hasBlanks

Returns 1 when at least one of the arguments is blank.

hasBlanks(a,b,c...)

isNull

Returns 1 when c is unspecified (null).

isNull(c)

isNullOrBlank

Returns 1 when c is unspecified (null) or blank (empty or only whitespaces).

isNullOrBlank(c)

Check text

Function Name	Description	Syntax	Example
contains	Returns 1 if c contains s, 0 if it doesn't. Case is ignored.	contains(c, s)	contains('Xpertdoc', 'Xp') = 1 contains('Xpertdoc', 'xP') = 1 contains('Xpertdoc', 'Xu') = 0 contains('Xpertdoc', 'u') = 0
containsc	Returns 1 if c contains s, 0 if it doesn't. Case-sensitive.	containsc(c, s)	containsc('Xpertdoc', 'Xp') = 1 containsc('Xpertdoc', 'xP') = 0 containsc('Xpertdoc', 'Xu') = 0 containsc('Xpertdoc', 'u') = 0
startsWith	Returns 1 if c starts with s, 0 if it doesn't. Case is ignored.	startsWith(c, s)	startsWith('Xpertdoc', 'xp') = 1 startsWith('Xpertdoc', 'X') = 1 startsWith('Xpertdoc', 'oc') = 0 startsWith('Xpertdoc', 'Oc') = 0
startsWithC	Returns 1 if c starts with s, 0 if it doesn't. Case-sensitive.	startsWithC(c, s)	startsWithC('Xpertdoc', 'xp') = 0 startsWithC('Xpertdoc', 'X') = 1 startsWithC('Xpertdoc', 'oc') = 0 startsWithC('Xpertdoc', 'Oc') = 0
endsWith	Returns 1 if c ends with s, 0 if it doesn't. Case is ignored.	endsWith(c, s)	endsWith('Xpertdoc', 'xp') = 0 endsWith('Xpertdoc', 'X') = 0 endsWith('Xpertdoc', 'oc') = 1 endsWith('Xpertdoc', 'Oc') = 1
endsWithC	Returns 1 if c ends with s, 0 if it doesn't. Case-sensitive.	endsWithC(c, s)	endsWithC('Xpertdoc', 'xp') = 0 endsWithC('Xpertdoc', 'X') = 0 endsWithC('Xpertdoc', 'oc') = 1 endsWithC('Xpertdoc', 'Oc') = 0

Other

Function Name

Description

Syntax

Example

getLoopIndex

Returns the number of the current iteration, with the first iteration being 0. This can be useful in loops. Example: making your own numbered lists.

getLoopIndex()

If you have nested loops, you can get a higher level loop's current iteration by specifying how many levels up you want to look. For instance, if you have loops A, B and C where C is inside B and B is inside A:

Nested loop	Iteration
Inside of loop C	getLoopIndex() = return the iteration of loop C getLoopIndex(0) = return the iteration of loop C getLoopIndex(1) = return the iteration of loop B (1 level up) getLoopIndex(2) = return the iteration of loop A (2 levels up)
Inside of loop B	getLoopIndex() = return the iteration of loop B getLoopIndex(0) = return the iteration of loop B getLoopIndex(1) = return the iteration of loop A getLoopIndex(2) = invalid (there is no loop 2 levels up)
Inside of loop A	getLoopIndex() = return the iteration of loop A getLoopIndex(0) = return the iteration of loop A getLoopIndex(1) = invalid (there is no loop 1 level up) getLoopIndex(2) = invalid (there is no loop 2 levels up)

safeValue

Returns c unless an error occurs, in which case d is returned. If d is not specified, null is used. This function can be useful when you cannot guarantee that the value will be what you need. For instance, further calculations expect a number, but the field may also hold a string. Using this function guarantees that the document generation continues - it may not be with the expected result, but at least you can catch this instead of running into an error.

safeValue(c,d)

valueOrDefault

Returns c unless it is null, in which case d is returned. This function can be useful when you must avoid null values. Null values are, for instance, empty fields (for numbers or dates).

valueOrDefault(c,d)

Date & Time Functions

Smart Flows comes with a large number of date & time functions, which can be used to do calculations with dates and times.

Concept

All of the date & time calculations are done using functions. Smart Flows has a long list of functions that you can use in expressions. Expressions are accessed via pseudo-fields or formulas.

It's good to read up on expressions so that you have a basic understanding of how to use them properly. This will allow you to dive right in to the date & time functions.

Base date

Some functions require that you specify a base date. A base date is necessary for some functions that deal with months/years, as not every month/year has the same length. Changing the base date can then also change the output of those functions.

For instance, the function formatPeriod can be used to convert a time period into a number of months and days. Here's how the base date can change the output; in the following functions, the final parameter is the base date, and that's the only parameter that's changed:

formatPeriod(8264000000,'M \'months and \'d \'days\'','2016-01-01') = 3 months and 4 daysformatPeriod(8264000000,'M \'months and \'d \'days\'','2016-02-01') = 3 months and 5 daysformatPeriod(8264000000,'M \'months and \'d \'days\'','2016-03-01') = 3 months and 3 days

The difference here is due to the month of February being shorter. If you don't specify a base date, the current date is used.

Examples

While this page contains all the info you'll need on these functions, you may be wondering how to use them in your specific use case. Therefore, we've described a few examples.

Functions

Current

Use these functions to get the current date, time or datetime.

Name	Input	Output
currentDate	none	The current date.
currentTime	none	The current time.
currentDateTime	none	The current date & time.

The output of these functions is in the dateTime format as described on Date & time formats.

Add or subtract

Add days, months or years

Use the following functions to add a number of days/months/years to a date or datetime.

Name & Arguments	Input	Output
addDays(datetime, amount)	date: a dateTime amount: the number of days to add	The provided dateTime with a number of days added.
addDaysToDate(date, amount)	date: a date amount: the number of days to add	The provided date with a number of days added.
addMonths(datetime, amount)	date: a dateTime amount: the number of months to add	The provided dateTime with a number of months added.
addMonthsToDate(date, amount)	date: a date amount: the number of months to add	The provided date with a number of months added.
addYears(datetime, amount)	date: a dateTime amount: the number of years to add	The provided dateTime with a number of years added.
addYearsToDate(date, amount)	date: a date amount: the number of years to add	The provided date with a number of years added.

The output of these functions is either in the dateTime or the date format. Both are described on Date & time formats.

Subtract days, months or years

Use the following functions to subtract a number of days/months/years from a date or datetime.

Name & Arguments	Input	Output
subDays(datetime, amount)	date: a dateTime amount: the number of days to subtract	The provided dateTime with a number of days subtracted.
subDaysFromDate(date, amount)	date: a date amount: the number of days to subtract	The provided date with a number of days subtracted.
subMonths(datetime, amount)	date: a dateTime amount: the number of months to subtract	The provided dateTime with a number of months subtracted.
subMonthsFromDate(date, amount)	date: a date amount: the number of months to subtract	The provided date with a number of months subtracted.
subYears(datetime, amount)	date: a dateTime amount: the number of years to subtract	The provided dateTime with a number of years subtracted.
subYearsFromDate(date, amount)	date: a date amount: the number of years to subtract	The provided date with a number of years subtracted.

The output of these functions is either in the dateTime or the date format. Both are described on Date & time formats.

Add seconds, minutes or hours

Use the following functions to add a number of seconds/minutes/hours to a time or datetime.

Name & Arguments	Input	Output
addSeconds(datetime, amount)	time: a dateTime amount: the number of seconds to add	The provided dateTime with a number of seconds added.
addSecondsToTime(time, amount)	time: a time amount: the number of seconds to add	The provided time with a number of seconds added.
addMinutes(datetime, amount)	time: a dateTime amount: the number of minutes to add	The provided dateTime with a number of minutes added.
addMinutesToTime(time, amount)	time: a time amount: the number of minutes to add	The provided time with a number of minutes added.
addHours(datetime, amount)	time: a dateTime amount: the number of hours to add	The provided dateTime with a number of hours added.
addHoursToTime(time, amount)	time: a time amount: the number of hours to add	The provided time with a number of hours added.

The output of these functions is either in the dateTime or the time format. Both are described on Date & time formats.

Subtract seconds, minutes or hours

Use the following functions to subtract a number of seconds/minutes/hours from a time or datetime.

Name & Arguments	Input	Output
subSeconds(datetime, amount)	time: a dateTime amount: the number of seconds to subtract	The provided dateTime with a number of seconds subtracted.
subSecondsFromTime(time, amount)	time: a time amount: the number of seconds to subtract	The provided time with a number of seconds subtracted.
subMinutes(datetime, amount)	time: a dateTime amount: the number of minutes to subtract	The provided dateTime with a number of minutes subtracted.
subMinutesFromTime(time, amount)	time: a time amount: the number of minutes to subtract	The provided time with a number of minutes subtracted.
subHours(datetime, amount)	time: a dateTime amount: the number of hours to subtract	The provided dateTime with a number of hours subtracted.
subHoursFromTime(time, amount)	time: a time amount: the number of hours to subtract	The provided time with a number of hours subtracted.

The output of these functions is either in the dateTime or the time format. Both are described on Date & time formats.

Format according to a pattern

Use these functions to turn a date, time, datetime or period into a text string according to a given pattern.

This has the same effect as using the custom date option in the mapping window as described on this page:

Name & Arguments	Input	Output
formatDate(date, pattern)	date: a date pattern: a pattern, specified according to the rules on Date & time patterns	The provided date, in the provided pattern.
formatDateR(date, pattern, locale)	date: a date pattern: a pattern, specified according to the rules on Date & time patterns locale: a locale	The provided date, in the provided pattern, according to the provided locale.
formatDateTime(date, pattern)	date: a dateTime pattern: a pattern, specified according to the rules on Date & time patterns	The provided dateTime, in the provided pattern.
formatDateTimeR(date, pattern, locale)	date: a dateTime pattern: a pattern, specified according to the rules on Date & time patterns locale: a locale	The provided dateTime, in the provided pattern, according to the provided locale.
formatTime(time, pattern)	date: a time pattern: a pattern, specified according to the rules on Date & time patterns	The provided time, in the provided pattern.
formatTimeR(time, pattern, locale)	date: a time pattern: a pattern, specified according to the rules on Date & time patterns locale: a locale	The provided time, in the provided pattern, according to the provided locale.
formatPeriod(period, pattern, base date)	period: a period pattern: a pattern, specified according to the rules on Date & time patterns base date: from which date to start the period (if not specified, the current date is used).	The provided period, in the provided pattern, starting from the base date.
formatPeriodIso(period, base date)	period: a period base date: from which date to start the period (if not specified, the current date is used).	The provided period, in the ISO8601 pattern (explained on Date & time formats), starting from the base date.

Convert a value into

Use these functions to convert a text string into a date, time or datetime. This is pretty much the opposite of the "format" functions, where a date, time or datetime was converted into a string.

The primary use of these functions is to turn something that looks like a date or time into a real date or time. For instance: if you have a field with 2015-07-03 as value, you may be tempted to think that this is a date, but it is a text string. If you want to use that text string as a real date (for calculations or for further formatting), you'll need to convert it into a real date first.

Name & Arguments	Input	Output
toDate(text string)	text string: a text string that represents a date	The provided text string converted into a date, according to the rules on Date & time formats.
toDateF(text string, pattern)	text string: a text string that represents a date pattern: a pattern, specified according to the rules on Date & time patterns	The provided text string converted into a date, according to the provided pattern.
toDateFR(text string, pattern, locale)	text string: a text string that represents a date pattern: a pattern, specified according to the rules on Date & time patterns locale: a locale	The provided text string converted into a date, according to the provided pattern, using the provided locale.
toDateTime(text string)	text string: a text string that represents a datetime	The provided text string converted into a datetime, according to the rules on Date & time formats.
toDateTimeF(text string, pattern)	text string: a text string that represents a datetime pattern: a pattern, specified according to the rules on Date & time patterns	The provided text string converted into a datetime, according to the provided pattern.
toDateTimeFR(text string, pattern, locale)	text string: a text string that represents a datetime pattern: a pattern, specified according to the rules on Date & time patterns locale: a locale	The provided text string converted into a datetime, according to the provided pattern, using the provided locale.
toTime(text string)	text string: a text string that represents a time	The provided text string converted into a time, according to the rules on Date & time formats.
toTimeF(text string, pattern)	text string: a text string that represents a time pattern: a pattern, specified according to the rules on Date & time patterns	The provided text string converted into a time, according to the provided pattern.
toTimeFR(text string, pattern, locale)	text string: a text string that represents a time pattern: a pattern, specified according to the rules on Date & time patterns locale: a locale	The provided text string converted into a time, according to the provided pattern, using the provided locale.

Number of ... in period

Use these functions to convert a period into a number representing the number of seconds, minutes, hours or days in the period.

Months and years are not supported due to their variable length.

Name & Arguments	Input	Output
toSeconds(period)	period: a period	The number of seconds in the provided period.
toMinutes(period)	period: a period	The number of minutes in the provided period.
toHours(period)	period: a period	The number of hours in the provided period.
toDays(period)	period: a period	The number of days in the provided period.

These functions return a decimal number, which may not always be practical. You can make use of the ceil and floor functions to respectively round up or down, like this:

toDays(5000000000) = 57.87037037floor(toDays(5000000000)) = 57ceil(toDays(5000000000)) = 58

First or last of month

Use these functions to find out the first or last day of the month.

Name & Arguments	Input	Output
firstDateOfMonth(date)	date: a date (if not specified, the current date is used)	A dateTime on the first day of the month of the provided date. The time is left unchanged.
lastDateOfMonth(date)	date: a date (if not specified, the current date is used)	A dateTime on the last day of the month of the provided date. The time is left unchanged.
lastDayOfMonth(date)	date: a date (if not specified, the current date is used)	The number of the last day of the month.

Create a period

Use these functions to create a period. This period can then be used in other functions that require a period as input.

Name & Arguments	Input	Output
createPeriod(years, months, weeks, days, hours, minutes, seconds, milliseconds, base date)	years: the number of years in the period months: the number of months in the period weeks: the number of weeks in the period days: the number of days in the period hours: the number of hours in the period minutes: the number of minutes in the period seconds: the number of seconds in the period milliseconds: the number of milliseconds in the period base date: from which date to start the period (if not specified, the current date is used).	A period spanning the sum of all the provided time elements.
createPeriodDHM(days, hours, minutes, base date)	days: the number of days in the period hours: the number of hours in the period minutes: the number of minutes in the period base date: from which date to start the period (if not specified, the current date is used).	A period spanning the sum of all the provided time elements.
createPeriodYMD(years, months, days, base date)	years: the number of years in the period months: the number of months in the period days: the number of days in the period base date: from which date to start the period (if not specified, the current date is used).	A period spanning the sum of all the provided time elements.
createPeriodIso(value, base date)	value: a text string that is a valid ISO8601 period. base date: from which date to start the period (if not specified, the current date is used).	A period spanning the sum of all the time elements in the provided ISO8601 period.
periodBetween(date 1, date 2)	date 1: a date, time or datetime date 2: a date, time or datetime	A period spanning the time between the two provided dates/datetimes/times.

Break a period down into parts

Use these functions to break a period down into its parts.

The output of these functions is the number of years, months, days, hours, minutes or seconds in a period, without going over the limit. This means that you can't ever have 15 months (11 max), 72 minutes (59 max) etc. So if you have a period of about 2 years, using the getPeriodMinutes function on its own makes no sense, as it will always return 59.

Therefore, using these functions only makes sense if you use them together. Here are a few typical use cases:

You have been a member of this community for x years, x months and x days.
There are x days and x hours left until your subscription will be cancelled automatically. (if the subscription can be longer than 1 month, you should add x months as well)

For cases such as these, these functions are very useful as you do not have to calculate the overflow (e.g. 14 months = 1 year + 2 months) yourself.

Name & Arguments	Input	Output
getPeriodYears(period, base date)	period: a period base date: from which date to start the period (if not specified, the current date is used).	The number of years in the provided period, calculated from the base date.
getPeriodMonths(period, base date)	period: a period base date: from which date to start the period (if not specified, the current date is used).	The number of months (maximum 11) in the provided period, calculated from the base date.
getPeriodDays(period, base date)	period: a period base date: from which date to start the period (if not specified, the current date is used).	The number of days (maximum 30/29/28/27, depending on the base date) in the provided period, calculated from the base date.
getPeriodHours(period, base date)	period: a period base date: from which date to start the period (if not specified, the current date is used).	The number of hours (maximum 23) in the provided period, calculated from the base date.
getPeriodMinutes(period, base date)	period: a period base date: from which date to start the period (if not specified, the current date is used).	The number of minutes (maximum 59) in the provided period, calculated from the base date.
getPeriodSeconds(period, base date)	period: a period base date: from which date to start the period (if not specified, the current date is used).	The number of seconds (maximum 59) in the provided period, calculated from the base date.

Locales

A locale is a set of parameters that defines the user's language, country and any special variant preferences. Smart Flows uses these parameters to interpret or output data in a certain way.

You can find a list of supported locales on this page.

Here are a few examples of locales:

en - English
en_US - English (United States)
nl - Dutch
de_AT - German (Austria)

When using locales as arguments in functions, make sure to put them in between apostrophes, like 'en_US'.

Examples of Date & Time Functions

Smart Flows comes with a large number of date & time functions - this page shows a few examples to give you an idea of how to use them.

Add days to date

If you want to add 5 days to the current date:

addDaysToDate(currentDate(),5)

If you want to add 5 days to a certain field that contains a datetime:

addDays(myfield,5)

Where myfield is a field inserted via the Insert Field button in the expression editor.

Format a date in a certain way

If you want to format the current date like July 7, 2015 / August 23, 2012 / November 11, 2016 etc:

formatDate(currentDate(),'MMMM d, yyy')

More information on how to define patters can be found on Date & time patterns.

Get the number of days between two dates

If you want to get the number of days between two fields that both contain datetimes:

floor(toDays(periodBetween(fieldA,fieldB)))

Where fieldA and fieldB are fields inserted via the Insert Field button in the expression editor.

Explanation:

periodBetween returns a period spanning the time between the two provided fields.
toDays converts that period into a decimal number representing the number of days.
floor rounds that number down so that you always end up with whole days.

Convert a text string, representing a date in a certain locale, into a usable date

If you want to convert the text string 8 juin 2009 (this is French) that is stored in the myfield field into a usable date such as 08-06-2009:

toDateFR(myfield,'d MMMM yyyy','fr')

Where myfield is a field inserted via the Insert Field button in the expression editor.

Explanation:

d MMMM yyyy is the pattern that follows from 8 juin 2009. It's important that you match your pattern to the content of the field. If you field contains 2009/juin/08, use yyyy/MMMM/dd as the pattern. More info on Date & time patterns.
fr is the locale for general French.

Find the current day

If you want to see today's number (as in: now it's September 25th, so 25):

formatDate(currentDate(),'d')

Explanation: this works because d is the pattern for the day - so you're basically asking Smart Flows to format the current date into just one number: that of the day.

Find the next month

If you want to see the number of the next month (for instance: now it's October (10), so it would output 11):

formatDate(addMonthsToDate(currentDate(),1),'M')

Explanation:

addMonthsToDate(currentDate(),1) will return the date equal to today + 1 month. If it's October 5th, it will return November 5th.
formatDate( ... , 'M') will return the month. So if we ask for the month of today + 1 month, it will return next month.

Display the first day of the next month

If you want to display a line that shows the date of the first day of the next month, like Thursday 1 December, 2016 when it's currently November 2016:

formatDate(firstDateOfMonth(addMonthsToDate(currentDate(),1)),'EEEE d MMMM, yyyy')

Explanation:

addMonthsToDate(currentDate(),1) will return the date equal to today + 1 month. If it's November 25th, it will return December 25th.
firstDateOfMonth(...) will return the date as if it was the first day of the current month.
formatDate(...) will return the date in the correct formatting.

Compare dates

If you want to check whether a date (fieldA) comes before another date (fieldB):

lt(fieldA,fieldB)

Where fieldA and fieldB are fields inserted via the Insert Field button in the expression editor.

Explanation: lt(a,b) returns true when a is lower than b, otherwise it returns false. This also works for dates.

'lt' stands for 'lower than' and this is just one of many functions you can use to compare values:

eq - check whether 2 values are equal
neq - check whether 2 values are different
gt - check whether the first value is greater than the second value
geq - check whether the first value is greater than or equal to the second value
lt - check whether the first value is lower than the second value
lteq - check whether the first value is lower than or equal to the second value

Create a period of a number of days

If you want to create a period of 60 days:

createPeriodDHM(60,0,0)

This is pretty useful to set up in a pseudo-field, after which you can then refer to the value of that pseudo-field in other expressions. Should the length of the period ever change, you can then just change it once in the pseudo-field instead of having to change it in every expression.

Break down a period into months and days

If you want to present the recipient of your document with a text string that reads "There are x months and x days left on your trial" where the period is stored in the myfield field:

concat('There are ',getPeriodMonths(myfield),' months and ',getPeriodDays(myfield),' days left on your trial')

Where myfield is a field inserted via the Insert Field button in the expression editor.

Explanation:

getPeriodMonths and getPeriodDays extract the number of months and days in a period, taking into account overflow of days into months (e.g. 35 days = 1 month and 5 days).
concat allows you to put text strings together:
- 'There are '
- x
- ' months and '
- x
- ' days left on your trial'

Break down a period into months and days (advanced)

We can make the previous example even better by adding a conditional that checks whether there actually is a month left. If there is no month left, you could end up with "There are 0 months and 20 days left on your trial."

It would be better to address this by adding a conditional, like so:

if(lt(getPeriodMonths(myfield),1),concat('There are ',getPeriodDays(myfield),' days left on your trial'),concat('There are ',getPeriodMonths(myfield),' months and ',getPeriodDays(myfield),' days left on your trial'))

Not really readable, so use the Pretty print button in the expression editor to make it more readable:

if(
lt(
getPeriodMonths(myfield),
1
),
concat(
'There are',
getPeriodDays(myfield),
'days left on your trial'
),
concat(
'There are',
getPeriodMonths(myfield),
'months and',
getPeriodDays(myfield),
'days left on your trial'
)
)

Explanation:

if(cond,a,b) will display a if the condition cond is true and b if it is not true.
lt(a,b) returns true when a is lower than b, otherwise it returns false.

For more information about these functions, check out List of functions.

Display the first day of the next month without using the first/last functions (advanced)

This was written before the firstDateOfMonth, lastDateOfMonth and lastDayOfMonth functions existed and this can be solved much more easily (see above). But we'll leave it here as it's a good example of how creative you can get with Smart Flows' functions.

If you want to display a line that shows the date of the first day of the next month, like Thursday 1 December, 2016 when it's currently November 2016:

formatDate(subDaysFromDate(addMonthsToDate(currentDate(),1),formatDate(addMonthsToDate(currentDate(),1 ),'d')-1),'EEEE d MMMM,yyyy')

Not really readable, so use the Pretty print button in the expression editor to make it more readable:

formatDate(
    subDaysFromDate(
        addMonthsToDate(
             currentDate(),
             1
           ),
           formatDate(
               addMonthsToDate(
                currentDate(),
                1
           ),
           'd'
         )-1
      ),
      'EEEE d MMMM, yyyy'     
 )

Explanation:

formatDate(addMonthsToDate(currentDate(),1),'d') returns what day it will be one month from now. This may differ from the current day when you try to add one month to January 30th - as then you would get February 28th or 29th. So we add one month to today and then ask Smart Flows to give us what day it will be in one month.
Once we have that day, we subtract 1 from it. So if our previous result gives December 15th, we end up with the number 14.
subDaysFromDate(addMonthsToDate(currentDate(),1), ... ) where ... is the result we got earlier (14 in our example). This series of functions will add one month to today and then subtract a number of days from that date. In our example, it will subtract 14 days from that date.
formatDate( ... , 'EEEE d MMMM, yyyy') where ... is all of the previous, returns the date in the correct formatting. More info on Date & time patterns.

Automatically put Christmas wishes in a document (advanced)

If you want your document to read "Merry Christmas and a happy new year!", but only between December 5th and January 10th of every year:

if(or(and(eq(formatDate(currentDate(),'M'),12),gt(formatDate(currentDate(),'d'),4)),and(eq(formatDate(currentDate(),'M'),1),lt(formatDate(currentDate(),'d'),11))),'Merry Christmas and a happy new year!','')

Not really readable, so use the Pretty print button in the expression editor to make it more readable:

if(
    or(
        and(
            eq(
                 formatDate(
                     currentDate(),
                     'M'
                 ),
                 12
            ),
            12
        ),
        gt(
            formatdate(
                currentdate(),
                'd'
            ),
            4
        )
    ),
    and(
        eq(
            formatdate(
                currentDate(),
                'M'
            ),
            1
        ),
        lt(
            formatdate(
                currentDate(),
                'd'
            ),
            11
        )
    ),
),
    Merry Christmas and a happy new year!',
)

Explanation:

eq(formatDate(currentDate(),'M'),12) checks whether today's month is 12 (December).
gt(formatDate(currentDate(),'d'),4) checks whether today's day is greater than 4 (so 5 or higher).
These 2 checks are put together in an and(...) that requires both of them to be true.
eq(formatDate(currentDate(),'M'),1) checks whether today's month is 1 (January).
lt(formatDate(currentDate(),'d'),11) checks whether today's month is lower than 11 (so 10 or lower).
These 2 checks are put together in an and(...) that requires both of them to be true.
Both and() are put in an or(...) to check whether one of them is true.
if(or(...),'Merry Christmas and a happy new year!','' returns the sentence if the or(...) is true, and the empty string '' if it's not true.

Date & Time Formats

A lot of the date & time functions require a date, time, dateTime or period as input. While you'll often be providing a field or a calculated date, it is good to know how to specify these, as an invalid input will result in an error.

All of these are based on the ISO8601 standard. Note that not every specification of the standard is allowed.

With all of these formats, you need to specify valid dates, times, periods etc. You cannot use 2015-20-20 as a date as there is no 20th month. Or 05:90:30 as a time, since 59 is the maximum value for minutes.

Date

Dates are comprised of years, months and days, and their formatting is represented as follows:

yyyy means a year in 4 digits (0000-9999)
MM means a month in 2 digits (01-12)
dd means a day in 2 digits (01-31)

Note that you need to use the full digits. You cannot use 1 as January, you need to use 01. Same goes for days.

A value that represents a date may be formatted as follows:

yyyy-MM-dd (eg. 2015-09-30)
yyyy-MM (eg. 2015-09)
yyyy (eg. 2015)

Non-specified months are assumed to be January, and non-specified days are assumed to be the first day of the month. For example:

Formatting 2015 as a date will result in 2015-01-01
Formatting 2016-03 as a date will result in 2016-03-01

Time

Times are comprised of hours, minutes and seconds, and their formatting is represented as follows:

hh means an hour in 2 digits (00-23)
mm means a minute in 2 digits (00-59)
ss means a second in 2 digits (00-59)

Note that you need to use the full digits. So use 05 instead of 5, 02 instead of 2 etc.

A value that represents a time needs to be formatted as follows:

hh:mm:ss (eg. 05:30:21)

Since no time zone information is provided, all times specified like this are interpreted as local time. It is not recommended to use local time when dealing with other time zones, as 05:30:21 in Brussels is not the same as 05:30:21 in New York.

Time with time zone

When specifying a time, it may be useful to specify the time zone as well. Time zones are defined by specifying the UTC offset as follows:

hh:mm:ss-hh:mm for negative UTC offset (eg. 05:30:21-07:00)
hh:mm:ss+hh:mm for positive UTC offset (eg. 05:30:21+02:00)

It is also possible to provide UTC time (also known as Zulu time) by adding a Z at the end:

hh:mm:ssZ (eg. 05:30:21Z)

UTC time takes into account the current time zone. For instance, in Brussels, in winter, (which is UTC+1 in winter), 05:30:21Z is the same as 06:30:21. You can read more about UTC time here.

DateTime

A dateTime is a date and a time put together. Specifying a dateTime is done by putting a T in between the date and time. A value that represents a dateTime may be formatted as follows:

yyyy-MM-ddThh:mm:ss (eg. 2015-09-30T05:30:21)
yyyy-MM-ddThh:mm:ssZ for UTC time
yyyy-MM-ddThh:mm:ss-hh:mm for negative UTC offset (eg. 2015-09-30T05:30:21-07:00)
yyyy-MM-ddThh:mm:ss+hh:mm for positive UTC offset (eg. 2015-09-30T05:30:21+02:00)
yyyy-MM-dd (eg. 2015-09-30)
yyyy-MM (eg. 2015-09)
yyyy (eg. 2015)

Any omitted parameters will be set to their default value: month to January, day to 01 and all hours, minutes and seconds to 00.

Period (milliseconds)

A period is a length of time. It is defined by specifying a number of milliseconds. For instance:

86,400,000 = 1 day
1,145,700,000 = 12 days, 30 minutes and 15 seconds

Specifying periods in milliseconds is highly impractical (it's just how it's done in the background) so there are functions you can use to help you define your period, such as createPeriod.

Period (ISO)

Another method of specifying a period is using the ISO8601 standard, by using the createPeriodIso function. Instead of using milliseconds, you can specify each time element separately. For instance:

P1Y2M5W8DT4H15M3S = 1 year, 2 months, 5 weeks, 8 days, 4 hours, 15 minutes and 3 seconds

That is the full specification, but you can omit time elements:

P1YT1H = 1 year and 1 hour
P15DT5M = 15 days and 5 minutes
PT5M10S = 5 minutes and 10 seconds

It's important that you follow these rules when specifying a period in the ISO8601 standard:

Follow this notation: PxYxMxWxDTxHxMxS where
- P tells the computer that you're defining a period (mandatory)
- xY is the number (x) of years (optional)
- xM is the number (x) of months (optional)
- xW is the number (x) of weeks (optional)
- xD is the number (x) of days (optional)
- T tells the computer that the rest is about hours, minutes and seconds (optional)
- xH is the number (x) of hours (optional)
- xM is the number (x) of minutes (optional)
- xS is the number (x) of seconds (optional)
If you want to use hours, minutes or seconds in your period definition, you need to add T before the first hour/minute/second element.
Stick to the order of the notation, also when leaving out time elements.
There are no limits to the value of a time element. For instance, you can define PT5000H for defining 5000 hours.

Date & Time Patterns

This page explains how to define a pattern to output a certain date, time or dateTime in a certain way. You specify the pattern according to the rules and elements specified below.

You can specify custom date & time patterns in the date & time functions, or in the custom date option as described on this page:

Elements of a pattern

A pattern consists of time elements and, optionally, static elements. Time elements represent a time unit, such as a year, a month, a day, but also an AM/PM notation, a timezone etc.

For instance:

yyyy-MM-dd = 2015-07-15 for July 15th in 2015. Both - are static elements, all the rest are time elements.
dd/MM/yyyy = 15/07/2015 for July 15th in 2015. Both / are static elements, all the rest are time elements.
dd 'days' = 30 days for a period of 30 days. The word days is static, dd is the time element representing days.

Using both time and static elements allows you to make your own custom date & time patterns.

Time elements

You can use the following time elements in the pattern by just putting their symbol directly into the pattern.

We'll use the following datetime as the example: 2015, July 15th, 03:45:30. Locale: English (en). Time zone: Pacific Standard Time

symbol	meaning	example	extra info
G	era	AD
u	year (4 digits)	2015	This is a year as most people and businesses use it.
y	year (2 digits)	15 (2015)	This is a year as most people and businesses use it.
Y	week year (4 digits)	2015	A week year is a year where all the weeks are whole weeks.
w	week year (2 digits)	15 (2015)	A week year is a year where all the weeks are whole weeks.
M	month (1 digit if possible)	7
MM	month (2 digits)	07
MMM	month (short notation)	Jul	Depends on the locale.
MMMM	month (long notation)	July	Depends on the locale.
w	week in year (1 digit if possible)	29	29 = the 29th week in the year
ww	week in year (2 digits)	29	29 = the 29th week in the year
W	week in month (1 digit if possible)	3	3 = the 3rd week in July 2015
WW	week in month (2 digits)	03	3 = the 3rd week in July 2015
D	day in year (1 digit if possible)	196
DD	day in year (2 digits if possible)	196
DDD	day in year (3 digits)	196
d	day in month (1 digit if possible)	15
dd	day in month (2 digits)	15
F	day of week in month (number)	3	3 = the 3rd wednesday in July 2015
u	day of week in week (number)	3	3 = the 3rd day of the week
E	day of week (short notation)	Wed	Depends on the locale.
EEEE	day of week (long notation)	Wednesday	Depends on the locale.
a	AM/PM marker	AM
H	hour in day (0-23) (1 digit if possible)	3
HH	hour in day (0-23) (2 digits)	03
k	hour in day (1-24) (1 digit if possible)	3
kk	hour in day (1-24) (2 digits)	03
K	hour in AM/PM (0-11) (1 digit if possible)	3
KK	hour in AM/PM (0-11) (2 digits)	03
h	hour in AM/PM (1-12) (1 digit if possible)	3
hh	hour in AM/PM (1-12) (2 digits)	03
m	minute in hour (1 digit if possible)	45
mm	minute in hour (2 digits)	45
s	second in minute (1 digit if possible)	30
ss	second in minute (2 digits)	30
S	millisecond (1 digit if possible)	0
SS	millisecond (2 digits if possible)	00
SSS	millisecond (3 digits)	000
z	time zone (general) (short notation)	PST
zzzz	time zone (general) (long notation)	Pacific Standard Time
Z	time zone (RFC 822)	-0800
X	time zone (ISO 8601) (short)	-08
XX	time zone (ISO 8601) (normal)	-0800
XXX	time zone (ISO 8601) (long)	-08:00

Static elements

You can use both symbols and letters as static elements. You can use the following symbols by putting them directly in the pattern:

/ * - + & ! ( ) { } [ ] , . ; : < > _ - # @

If you want to use a ' (apostrophe), you need to put 2 of them, like so:

If you want to use letters, you need to put them in between apostrophes, like so:

'a' 'days' 'a whole sentence'

Putting that all together, if you want your pattern to say 7 o'clock (where 7 is the hour), here's the pattern you need to specify:

h 'o''clock'

Defining a pattern

In the mapping window

When you have a date in a field, or as the output of an expression, you can use the custom date option in the Format tab of the mapping window to specify a pattern:

Just start typing into the box next to Pattern or select the ... to select a pre-made pattern, that you can then adjust.

In an expression

Defining a pattern in an expression is necessary for quite a few functions, such as formatDate, formatTime, toDateF, toTimeF etc. For instance:

formatDate(currentDate(),'yyyy-MM-dd')formatTime(currentTime(),'hh:mm')

As always with expressions, you need to put strings (such as a date & time pattern) in between ' ' (apostrophes).

Note: This, however, makes it a bit harder to use letters in your patterns, as you can't simply use apostrophes, since those are used to delineate the beginning and ending of the pattern in an expression.

To remedy this, you can escape your apostrophes by putting a \ (backslash) in front of them. So, instead of...

dd 'days'h 'o''clock'

...you type this:

dd \'days\'h \'o\'\'clock\'

It may look a bit strange, but it will definitely get the job done. Here are examples of the above patterns. Notice the apostrophe before and after the entire pattern:

formatPeriod(10000000,'dd \'days\'')

formatDate(currentDate(),'h \'o\'\'clock\'')

Examples

Let's end with a few examples. For all of the following, the given date and time are 2001-07-04 12:08:56 local time in the U.S. Pacific Time time zone.

In the mapping window

These patterns don't require apostrophes like expressions would.

pattern	output
yyyy.MM.dd G 'at' HH:mm:ss z	2001.07.04 AD at 12:08:56 PDT
EEE, MMM d, ''yy	Wed, Jul 4, '01
h:mm a	12:08 PM
hh 'o''clock' a, zzzz	12 o'clock PM, Pacific Daylight Time
K:mm a, z	0:08 PM, PDT
yyyyy.MMMMM.dd GGG hh:mm aaa	02001.July.04 AD 12:08 PM
EEE, d MMM yyyy HH:mm:ss Z	Wed, 4 Jul 2001 12:08:56 -0700
yyMMddHHmmssZ	010704120856-0700
yyyy-MM-dd'T'HH:mm:ss.SSSZ	2001-07-04T12:08:56.235-0700
yyyy-MM-dd'T'HH:mm:ss.SSSXXX	2001-07-04T12:08:56.235-07:00
YYYY-'W'ww-u	2001-W27-3

In an expression

The following patterns include the apostrophes used in an expression to delineate the beginning and ending of a string.

pattern	output
'yyyy.MM.dd G \'at\' HH:mm:ss z'	2001.07.04 AD at 12:08:56 PDT
'EEE, MMM d, \'\'yy'	Wed, Jul 4, '01
'h:mm a'	12:08 PM
'hh \'o\'\'clock\' a, zzzz'	12 o'clock PM, Pacific Daylight Time
'K:mm a, z'	0:08 PM, PDT
'yyyyy.MMMMM.dd GGG hh:mm aaa'	02001.July.04 AD 12:08 PM
'EEE, d MMM yyyy HH:mm:ss Z'	Wed, 4 Jul 2001 12:08:56 -0700
'yyMMddHHmmssZ'	010704120856-0700
'yyyy-MM-dd'T'HH:mm:ss.SSSZ'	2001-07-04T12:08:56.235-0700
'yyyy-MM-dd'T'HH:mm:ss.SSSXXX'	2001-07-04T12:08:56.235-07:00
'YYYY-\'W\'ww-u'	2001-W27-3