CIB merge technical documentation (EN): Supported field commands

CIB merge technical documentation (EN)

3. CIB merge as a document interpreter

3.5. Supported field commands

General
Overview of basic field commands
Supported text functions
Supported date functions
Rules for processing text and date functions

General

MS-Office provides about 85 different field commands that can be used to program dynamic documents. All commands that are not directly related to the formatting of text are evaluated and supported by the CIB merge component during a merge process.

The interpretation of the remaining field commands that are important for the layout/print display (e.g. (total) page numbering, print date, signature fields, etc.) is reserved for the CIB format/output component. CIB merge passes these field commands unfiltered in the result document.

From CIB merge version 3.13.7:
The same behavior applies to a calculation expression that contains references to table elements: The expression is passed to the result document unfiltered.

Specifically, this refers to these references: LEFT, RIGHT, ABOVE, BELOW.

Overview of basic field commands

The following field commands of a document are interpreted directly by the CIB merge component during a merge process and dynamically converted into the output file (the field commands here are only listed alphabetically and not described with their full syntax):

Field command	Description
COMPARE	Compares two values and returns the numeric value 1 if the comparison is true and 0 (zero) if the comparison is false.
DATA
DATE	Inserts the current date.
IF	Compares arguments under certain conditions. Can be extended optionally to a loop instruction.
INCLUDEPICTURE	Inserts a graphic from a file. Is only executed by CIB merge in conjunction with the call parameter --EMBED. Otherwise the interpretation is only carried out by CIB format/output.
INCLUDEPICTURE "?graphic file" (from CIB merge Version 3.9.174)	Returns 0 if the file does not exist, otherwise 1.
INCLUDETEXT	Inserts a text from a file. "Please note in this context the possible field switch NEWLISTID"
INCLUDETEXT "?text file"	Returns 0 if the file does not exist, otherwise 1.
INFO	Inserts information from the Properties dialog box.
MERGEFIELD	Defines a placeholder with a name that will be replaced by a delivered variable content later.
MERGEREC <Alias-Name>	Inserts the number that the current record has in the input file (or table) <alias name>.
MERGEREC ?<Alias-Name>	Returns 0 if there are no more datasets in the file (or table) <alias name>.
MERGEREC #<Alias-Name> (from CIB merge version 3.12.1)	Returns the exact number of datasets in the file (or table) <alias name>. Attention: This function can be resource-intensive with CSV data sources!
MERGEREC ##<Alias-Name> (from CIB merge version 3.12.1)	Returns status information about the number of datasets in the file (or table) <alias name>. Values: 0 no dataset 1 one dataset 2 two or more datasets Attention: This function has no negative effect on the resources of CSV data sources.
NEXT	Moves to the next dataset or node in the data supply.
NEXTIF	Moves to the next dataset or node in the data supply when a certain condition is met.
QUOTE	Inserts text into the document.
REF	Defines a placeholder with a name that will be replaced by a delivered variable content later.
REF "?variable" (from CIB merge version 3.9.191)	Returns 0 if the variable does not exist, otherwise 1.
TO DO: REF DAT
TO DO: NEXT DEF
SET	Assigns new text to a bookmark.
SKIPIF	Skips a dataset or node in the data supply during a mail merge according to a condition. Usage with alias names is not possible!
TIME	Inserts the current time.
= Expression	Calculates the result of an expression (= formulas). CIB merge supports very extensive functions, even in complicated formulas. From CIB merge version 3.11.0 the text functions described in the following chapter are supported.

Some of these field commands allow an extension of their functionality with additional switches.

For example, loop instructions can be used to create dynamic tables of any length. It is also possible to insert text modules dynamically.

Supported text functions

From CIB merge version 3.12.0 the text functions described in the following chapter are supported.

Notes:

The possibility for linked (multiple) processing of text functions is described here.
Fixed texts (strings) can be used as parameters of a text function. The content of a string must be specified in quotation marks. The string itself must not contain quotation marks.

Text function (syntax)	Description
TRIM(text) Return value: Text	Deletes spaces in a text that are not individually between words and thus serve as separators. You can use TRIM for texts that you have copied from other application programs and that may contain unwanted spaces. Parameters: text (type text, required) The text, from which the spaces should be removed. Example: =Trim( " abc " ) results in abc
LEN(text) Return value: Number	Returns the number of characters in a string. Parameters: text (type text, required) The text from which you want to find out its length. Blank spaces count as characters. Example: = LEN( "ab abc" ) results in 6
FIND(find_text;within_text; start_num) Return value: Number	FIND searches for a character string within another and returns the position of the searched character string from the first character of the other character string. Parameters: find_text (type text, required) Sets the text to search. within_text (type text, required) Sets the text, in which you search with find_text. start_num (type number, required) Specifies the character to begin the search with. The first character corresponding to find_text has the number one 1. Example: =FIND( "b"; "abc", 1 ) results in 2 Notes: The FIND function is case sensitive and does not allow wildcard characters. If the search is not case-sensitive or you want to use wildcard characters, use search SEARCH. If find_text is an empty string (""), FIND determines a match with the first character of the character string to be searched (that is, with the character that occupies the position start_num or 1). find_text cannot contain placeholder characters(? or ) If find_text* cannot be found in within_text, FIND returns the error value -1. If First_character is smaller or equal to 0, FIND returns the error value -1. If start_num is greater then the lenght of Text, FIND returns the error value -1. Use start_num so that a search operation does not start with the first character of the string to be searched, but skips a certain number of characters. FIND always returns the number of characters from the beginning of within_text; the skipped characters are counted if start_num is greater than 1.
SEARCH(find_text; within_text; start_num) Return value: Number	SEARCH searches for a character string within another character string and returns as result the number of the starting position of the first character string, counted from the first character of the second character string. Parameters: find_text (type text, required) is the text you want to search for. A string specified as find_text can contain the placeholder characters question mark (?) and asterisk (). A question mark replaces one character, an asterisk replaces any character string. If you search for a question mark or an asterisk, you must enter two backslashes (\\) before the character you want to search for. within_text (type text, required) is the text in which to search for find_text. start_num (type number, required) Specifies the character at which the search should start. The first character belonging to find_text* has the number 1. Example: =SEARCH( "b"; "ABC", 1 ) results in 2 =SEARCH( "\\"; "ABCafkdl"; 1 ) results in 9 Notes: Use SEARCH to find out the position of a character or character string within another character string and change the latter using the MID or REPLACE function. SEARCH is not case sensitive. If you want to use a case-sensitive function, use FIND. If the string specified by find_text is not found, the function returns the error value -1. If start_num has a value less than 1 or greater than the length of within_text, the function returns the error value -1. Use start_num so that a search operation does not start with the first character of the string to be searched, but skips a certain number of characters. SEARCH always returns the number of characters from the beginning of within_text; the skipped characters are counted if start_num is greater than 1.
MID(text; start_num; num_chars) Return value: Text	The MID function returns a certain number of related characters from a given string. Parameters: text (type text, required) Text, to return characters from. start_num(type number, required) Character position in text, where the part to be extracted begins. num_chars(type number, required) Returns the number of characters that MID should return from text. Example: =MID( "abc"; 2; 1 ) results in b Notes: If start_num is greater than the total length of text, MID returns the string "" (empty string). If start_num is less than the total length of text, but start_num plus num_chars exceeds the total length of text, MID returns all characters up to the end of text. If start_num is less than 1, MID returns the string "" (empty string).
SUBSTITUTE(text; old_text; new_text; instance_num) Return value: Text	Replaces old with new text in a string. You can use SUBSTITUTE whenever you want to replace a specific character string within a text. You should always use REPLACE if you want to replace a character string starting at a specific position within a text. Parameters: text (type text, required) is the text with the characters to be replaced. old_text (type text, required) is the text to be replaced. new_text (type text, required) is the text to be replaced with old_text. instance_num (type number, required) Specifies how often old_text should be replaced with new_text. If you specify instance_num, only this occurrence of old_text will be replaced; otherwise, old_text will be replaced by new_text wherever it occurs in. Example: =SUBSTITUTE( "abcbcbcd"; "bc"; "d"; 2 ) results in addbcd
REPLACE(old_text; start_num; num_chars; new_text) Return value: Text	REPLACE replaces a character string of the specified length in the text from the specified position onwards with another character string. Parameters: old_text (type text, required) is the text, in which you want to replace an amount of characters. start_num (type number, required) The position of the character in old_text where to start replacing with new_text. num_chars (type number, required) The amount of characters in old_text, that REPLACE replaces with new_text. new_text (type text, required) The text with which you want to replace characters in old_text. Example: =REPLACE( "abcd"; 2; 2; "d" ) results in add
LOWER(text) Return value: Text	Converts a text to lower case. Parameters: text (type text, required) The text to convert in lower case. Example: =LOWER( "ABC9" ) results in abc9 Note: LOWER makes no changes to characters in the text that are not letters.
UPPER(text) Return value: Text	Converts a text to upper case. Parameters: text (type text, required) The text to convert in upper case. Example: = UPPER( "abc9" ) results in ABC9 Note: UPPER makes no changes to characters in the text that are not letters.
LEFT(text; num_chars) Return value: Text	Cuts the specified number of characters starting from the left. Parameters: text (type text, required) The text string with the characters to be cut. num_chars (type number, required) Amount of characters to be returned. Example: =LEFT( "abc"; 2 ) results in ab Notes: If num_chars is greater than the total length of text, LEFT returns the complete character string. If num_chars is smaller then 1, LEFT returns the error code -1.
RIGHT(text; num_chars) Return value: Text	Cuts the specified number of characters starting from the right. Parameters: text (type text, required) The text string with characters to cut. num_chars (type number, required) Amount of characters to be returned. Example: =RIGHT( "abc"; 2 ) results in bc Notes: If num_chars is greater than the total length text, RIGHT returns the complete character string. If num_chars is smaller then 1, RIGHT returns the error code -1.
REPT(text; number_times) Return value: Text	Repeats a text as often as specified. You can use REPT to repeat a text with a certain frequency. Parameters: text (type text, required) The text to be repeated. number_times (type number, required) A positive number indicating the number of repetitions Example: =REPT( "abc"; 2 ) results in abcabc Notes: If number_times = 0 is, an empty string „“ will be returned.
TOKENIZE(text; delimiters; tokenIdx) Return value: Text	Splits a text into individual text parts using the specified separators and returns the part specified with tokenIdx (e.g. with tokenIdx = 3, the third part). Parameters: text (type text, required) The text to separate. delimiters (type text, required) List of delimiters. If using a comma as separator, it needs to be masked with a backslash (\). tokenIdx (Type number, required) Number of the text part to be returned. If the index is negative, counting starts from the end of the text Examples: =TOKENIZE( "a b c-d"; " -"; 4 ) results in d =TOKENIZE( "a b-c d"; " -"; -4 ) results in a =TOKENIZE( "a b,c d"; " \,"; 3 ) results in c Notes: If the tokenIdx is too large or too small, the empty string "" is returned.
BEFORE(text; before_text) Return value: Text	Returns the characters of text that precede the first occurrence of before_text in text, or returns an empty string if before_text is not included in text. Parameters: text (type text, required) Is the text from which the string is to be taken. before_text (type text, required) The text to be searched for, which marks the end of the character string to be extracted. Example: =BEFORE( "abcdefg"; "cd" ) results in ab Notes: If before_text is not included in text, an empty string „“, will returned.
AFTER(text; after_text) Return value: Text	Returns the characters of text that follow the first occurrence of after_text in text, or returns an empty string if after_text is not included in text. Parameters: text (type text, required) Is the text from which the string is to be taken after_text (type text, required) The text to be searched, which marks the beginning of the character string to be extracted. Example: =AFTER( "abcdefg"; "cd" ) returns in efg Notes: If after_text is not contained in text, an empty string „“, will be returned.
FORMAT(text; format) Return value: Text	Returns text in the format format. This function works similar to formatting numbers. Parameters: text (type text, required) Is the text to format. format (type text, required) Defines the format. (Details see below) Valid characters to specify the format: x Placeholder characters for one character of the specified text. Adding spaces and separators: Space will be displayed exactly as entered / will be displayed exactly as entered ( will be displayed exactly as entered ) will be displayed exactly as entered . will be displayed exactly as entered - will be displayed exactly as entered Examples: =Format(„abcdefghi“;“xxx xxx xxx“) results in „abc def ghi“ Format(„abcdef“;“xxx-xxx-xxx“) results in „abc-def“ Format(„abcdefghi“;“xxx-xxx“) results „abc-defghi“ Note: if format is larger than text, text will be displayed from the left beginning in the defined format. after the last character of text the formatted text ends. if format is shorter then text, text will be displayed from the left beginning in the defined format, the rest of the string remains unchanged. spaces at the beginning and end of format are ignored.

Supported date functions

From version 3.12.12 CIB merge supports the date functions described below.

The date functions are defined analogous to calculation or text functions in the form
{ =DATUMSFUNKTION }

{ =DATE FUNCTION }
into the document.

A requirement for these functions is that the date used as input (or a variable "date") has a valid date value and is formatted in a valid date format.

Date function (syntax)	Description
ADDDAYS(Date,AmountDays) \@ Format Return: Date	Add days: A certain number of days is added to the value of the Date variable and output in a desired format. Parameters: Date See requirement above AmountDays Integer Format Has to be a valid syntax for date formatting Example: ADDDAYS(„15.02.2015“,14)\@ "tt.MM.jjjj" results in 01.03.2015
ADDDAYS_VD(VDatum,AnzTage) \@ Format Rückgabe: Datum	Tage addieren mit Valutadatum: Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet. Ansonsten siehe ADDDAYS. Beispiel: ADDDAYS_VD(„15.02.2015“,15)\@ "tt.MM.jjjj" ergibt 30.02.2015
ADDMONTHS(Date,AmountMonth) \@ Format Return: Date	Add month: A certain number of months is added to the value of the variable date and displayed in the desired format. Parameters: Date See requirements above AmountMonth Integer Format Has to be a valid syntax for date formatting Example: ADDMONTHS(„31.01.2015“, 1)\@ "tt.MM.jjjj" results in 03.03.2015
ADDMONTHS_VD(VDatum,AnzMonate) \@ Format Rückgabe: Datum	Monate addieren mit Valutadatum: Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet. Ansonsten siehe ADDMONTHS. Beispiel: ADDMONTHS_VD(„30.01.2015“, 1)\@ "tt.MM.jjjj" ergibt 30.02.2015
ADDYEARS(Date,AmountYears) \@ Format Return: Date	Add years: A certain number of years is added to the value of the variable date and output in a desired format. Parameters: Date See requirements above AmountYears Integer Format Has to be a valid syntax for date formatting Example: ADDYEARS(„31.01.2015“,10)\@ "tt.MM.jjjj" results in 31.01.2025
ADDYEARS_VD(VDatum,AnzJahre) \@ Format Rückgabe: Datum	Jahre addieren mit Valutadatum: Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet. Ansonsten siehe ADDYEARS. Beispiel: ADDMONTHS_VD(„29.02.2016“, 3)\@ "tt.MM.jjjj" ergibt ..29.02.2019
ADDWORKINGDAYS(Date,AmountDays, Holidays,WorkingDays) \@ Format Return: Date	Add working days: A certain number of working days is added to the value of the variable varDate and output in a desired format. Parameters: Date See requirements above AmoutDays Amount of days, integer Holidays Optionally, a list of public holidays can be specified, which are taken into account in the calculation. Syntax: dd.MM. Data separated by ";". WorkingDays Working days per week. Values between 2 and 7, default is 5. Or input of a bitmap, which indicates "1" for a working day. "0" for a day off. Examples: "1111110" – Sunday is a day off. "1111100" – Saturday and Sunday are off. Format Needs to have a valid syntax for date formatting Example: ADDWORKINGDAYS („01.01.2015“,5,"01.01.;6.1.;31.12.",5)\@ "tt.MM.jjjj" results in 09.01.2015
ADDWORKINGDAYS_VD(VDatum,AnzTage, Feiertage,Arbeitstage) \@ Format Rückgabe: Datum	Werktage addieren mit Valutadatum: Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet. Ansonsten siehe ADDWORKINGDAYS. Beispiel: ADDWORKINGDAYS_VD(„01.01.2015“,22,"01.01.;6.1.;31.12.",5)\@ "tt.MM.jjjj" ergibt 03.02.2015
GETNEXTWORKINGDAY(Date,Holidays,WorkingDays) \@ Format Return: Date	Next working day from date: Based on the value of the variable varDate, the next working day is determined taking the days off into account and output in the desired format. Parameters: Date See requirements above. Holidays Optionally, a list of public holidays can be specified, which are taken into account in the calculation. Syntax: dd.MM. Data separated by ";". WorkingDays Working days per week. Values between 2 and 7, default is 5. Or input of a bitmap, which indicates "1" for a working day. "0" for a day off. Examples: "1111110" – Sunday is a day off. "1111100" – Saturday and Sunday are off. Format Needs to have a valid syntax for date formatting Example: GETNEXTWORKINGDAY („01.01.2015“,"01.01.;6.1.;31.12.",5)\@ "tt.MM.jjjj" results in 02.01.2015
SUBTRACTDAYS(Date,AmountDays) \@ Format Return: Date	Subtract days: A certain number of days is subtracted from the value of the Date variable and displayed in the desired format. Parameters: Date See requirement above AmountDays Integer Format Has to be a valid syntax for date formatting Example: SUBTRACTDAYS(„15.02.2015“,14)\@ "tt.MM.jjjj" results in 01.02.2015
SUBTRACTDAYS_VD(VDatum,AnzTage) \@ Format Rückgabe: Datum	Tage subtrahieren mit Valutadatum: Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet. Ansonsten siehe SUBTRACTDAYS. Beispiel: SUBTRACTDAYS_VD(„01.03.2015“, 1)\@ "tt.MM.jjjj" ergibt 30.02.2015
SUBTRACTMONTHS(Date,AmountMonth) \@ Format Return: Date	Subtract months: A certain number of months is subtracted from the value of the variable Date and displayed in the desired format. Parameters: Date See requirement above AmountMonth Integer Format Has to be a valid syntax for date formatting Example: SUBTRACTMONTHS(„31.03.2015“,1)\@ "tt.MM.jjjj" results in 28.02.2015
SUBTRACTMONTHS_VD(VDatum,AnzMonate) \@ Format Rückgabe: Datum	Monate subtrahieren mit Valutadatum: Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet. Ansonsten siehe SUBTRACTMONTHS. Beispiel: SUBTRACTMONTHS_VD(„30.03.2015“, 1)\@ "tt.MM.jjjj" ergibt 30.02.2015
SUBTRACTYEARS(Date,AmountYears) \@ Format Return: Date	Subtract years: A certain number of years is subtracted from the value of the variable Date and output in the desired format. Parameters: Date See requirement above AmountYears Integer Format Has to be a valid syntax for date formatting Example: SUBTRACTYEARS(„31.01.2015“,10)\@ "tt.MM.jjjj" results in 31.01.2005
SUBTRACTYEARS_VD(VDatum,AnzJahre) \@ Format Rückgabe: Datum	Jahre subtrahieren mit Valutadatum: Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet. Ansonsten siehe SUBTRACTYEARS. Beispiel: SUBTRACTYEARS_VD(„29.02.2016“, 3)\@ "tt.MM.jjjj" ergibt 29.02.2013
SUBTRACTWORKINGDAYS(Date,AmountDays,Holidays,WorkingDays) \@ Format Return: Date	Subtract working days: A certain number of working days is subtracted from the value of the variable varDate and displayed in the desired format. Parameters: Date See requirements above AmountDays Amount of working days, integer Holidays Optionally, a list of public holidays can be specified, which are taken into account in the calculation. Syntax: dd.MM. Data separated by ";". Optionally, a list of public holidays can be specified, which are taken into account in the calculation. Syntax: dd.MM. Data separated by ";". WorkingDays Working days per week. Value between 2 and 7, default is 5. Oder input of a bitmap, which indicates "1" for a working day. "0" for a day off. Examples: "1111110" – Sunday is a day off. "1111100" – Saturday and Sunday are off. Format Has to be a valid syntax for date formatting Example: SUBTRACTWORKINGDAYS („08.01.2015“,3,"01.01.;6.1.;31.12.",5)\@ "tt.MM.jjjj" results in 02.01.2015
DATEDIF(Date1,Date2,Unit) Return: Integer	Calculate date difference: The difference between the date values Date1 and Date2 is calculated. The time unit is controlled by the Unit parameter. Parameters: Date1 Date2 See requirements above Unit Possible values: Y Difference in completed years M Difference in completed months D Difference in days MD Difference between the days (in days, ignoring month and year specification) YM Difference between the months (in months, ignoring day and year specification) YD Difference between days and months (in days, ignoring year specification) Example: DATEDIF("28.02.2015","01.03.2015",“Y“) results in 0
DATEDIF_VD(VDatum1,VDatum2,Einheit) Rückgabe: Ganzzahl	Datumsdifferenz berechnen mit Valutadatum: Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet. Ansonsten siehe DATEDIF. Beispiel: DATEDIF_VD("30.02.2015","03.03.2015",“Y“) ergibt 0
GETDAYDIFF(Date1,Date2) Return: Integer	Calculate difference in days: The difference between the date values Date1 and Date2 is calculated in days. Parameters: Date1 Date2 See requirements above Example: GETDAYDIFF("28.02.2015","01.03.2015") results in 1
GETDAYDIFF_VD(VDatum1,VDatum2) Rückgabe: Ganzzahl	Differenz in Tagen berechnen mit Valutadatum: Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet. Ansonsten siehe GETDAYDIFF. Beispiel: GETDAYDIFF_VD("28.02.2015","01.03.2015") ergibt 3
GETMONTHDIFF(Date1,Date2) Return: Integer	Calculate difference in months: The difference between the date values Date1 and Date2 is calculated in months. Parameters: Date1 Date2 See requirements above Example: GETMONTHDIFF("01.01.2015","31.01.2015") results in 0
GETMONTHDIFF_VD(VDatum1,VDatum2) Rückgabe: Ganzzahl	Differenz in Monaten berechnen mit Valutadatum: Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet. Ansonsten siehe GETMONTHDIFF. Beispiel: GETMONTHDIFF_VD("30.01.2015","30.02.2015") ergibt 1
GETYEARDIFF(Date1,Date2) Return: Integer	Calculate difference in years: The difference between the date values Date1 and Date2 is calculated in years. Parameters: Date1 Date2 See requirements above Example: GETYEARDIFF("31.12.2014","31.01.2015") results in 0
GETYEARDIFF_VD(VDatum1,VDatum2) Rückgabe: Ganzzahl	Differenz in Jahren berechnen mit Valutadatum: Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet. Ansonsten siehe GETYEARDIFF. Beispiel: GETYEARDIFF_VD("31.12.2014","30.02.2015") ergibt 0
GETFIRSTOFMONTH(Date) \@ Format Return: Date	First day of the month: The first day of the month of a given date is output in the desired format. Parameter: Date See requirements above Format Has to be a valid syntax for date formatting Example: GETFIRSTOFMONTH(„23.02.2015“)\@ "tt.MM.jjjj" results in 01.02.2015
GETFIRSTOFMONTH_VD(VDatum) \@ Format Rückgabe: Datum	Erster Tag im Monat berechnen mit Valutadatum: Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet. Ansonsten siehe GETFIRSTOFMONTH. Beispiel: GETFIRSTOFMONTH_VD(„30.02.2015“)\@ "tt.MM.jjjj" ergibt 01.02.2015
GETFIRSTOFNEXTMONTH(Date) \@ Format Return: Date	First day of next month: The first day of the next month of a given date is output in the desired format. Parameters: Date See requirements above Format Has to be a valid syntax for date formatting Example: GETFIRSTOFNEXTMONTH(„23.02.2015“)\@ "tt.MM.jjjj" results in 01.03.2015
GETFIRSTOFNEXTMONTH_VD(VDatum) \@ Format Rückgabe: Datum	Erster Tag des nächsten Monats berechnen mit Valutadatum: Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet. Ansonsten siehe GETFIRSTOFNEXTMONTH. Beispiel: GETFIRSTOFNEXTMONTH_VD(„30.02.2015“)\@ "tt.MM.jjjj" ergibt 01.03.2015
GETFIRSTOFQUARTER(Date) \@ Format Return: Date	First day of the quarter: The first day of the quarter of a given date is output in the desired format. Parameters: Date See requirements above Format Has to be a valid syntax for date formatting Example: GETFIRSTOFQUARTER(„23.02.2015“)\@ "tt.MM.jjjj" results in 01.01.2015
GETFIRSTOFQUARTER_VD(VDatum) \@ Format Rückgabe: Datum	Erster Tag des Quartals berechnen mit Valutadatum: Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet. Ansonsten siehe GETFIRSTOFQUARTER. Beispiel: GETFIRSTOFQUARTER_VD(„30.02.2015“)\@ "tt.MM.jjjj" ergibt 01.01.2015
GETLASTOFMONTH(Date) \@ Format Return: Date	Last day of the month: The last day of the month of a given date is output in the desired format. Parameters: Date See requirements above Format Has to be a valid syntax for date formatting Example: GETLASTOFMONTH(„23.02.2015“)\@ "tt.MM.jjjj" results in 28.02.2015
GETLASTOFMONTH_VD(VDatum) \@ Format Rückgabe: Datum	Letzter Tag des Monats berechnen mit Valutadatum: Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet. Ansonsten siehe GETLASTOFMONTH. Beispiel: GETLASTOFMONTH_VD(„23.02.2015“)\@ "tt.MM.jjjj" ergibt 30.02.2015
GETLASTOFLASTMONTH(Date) \@ Format Return: Date	Last day of last month: The last day of the previous month of a specified date is output in the desired format. Parameters: Date See requirements above Format Has to be a valid syntax for date formatting Example: GETLASTOFLASTMONTH(„23.02.2015“)\@ "tt.MM.jjjj" results in 31.01.2015
GETLASTOFLASTMONTH_VD(VDatum) \@ Format Rückgabe: Datum	Letzter Tag des letzten Monats berechnen mit Valutadatum: Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet. Ansonsten siehe GETLASTOFLASTMONTH. Beispiel: GETLASTOFLASTMONTH_VD(„23.03.2015“)\@ "tt.MM.jjjj" ergibt 30.02.2015
GETLASTOFQUARTER(Date) \@ Format Return: Date	Last day of the quarter: The last day of the quarter of a specified date is output in the desired format. Parameters: Date See requirements above Format Has to be a valid syntax for date formatting Example: GETLASTOFQUARTER(„23.02.2015“)\@ "tt.MM.jjjj" results in 31.03.2015

Date function (syntax)

Description

ADDDAYS(Date,AmountDays)

\@ Format

Return: Date

Add days:
A certain number of days is added to the value of the Date variable and output in a desired format.

Parameters:

Date See requirement above

AmountDays Integer

Format Has to be a valid syntax for date formatting

Example:
ADDDAYS(„15.02.2015“,14)\@ "tt.MM.jjjj" results in 01.03.2015

ADDDAYS_VD(VDatum,AnzTage)

\@ Format

Rückgabe: Datum

Tage addieren mit Valutadatum:

Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet.
Ansonsten siehe ADDDAYS.

Beispiel:
ADDDAYS_VD(„15.02.2015“,15)\@ "tt.MM.jjjj"
ergibt 30.02.2015

ADDMONTHS(Date,AmountMonth)

\@ Format

Return: Date

Add month:
A certain number of months is added to the value of the variable date and displayed in the desired format.

Parameters:

Date See requirements above

AmountMonth Integer

Format Has to be a valid syntax for date formatting

Example:
ADDMONTHS(„31.01.2015“, 1)\@ "tt.MM.jjjj" results in 03.03.2015

ADDMONTHS_VD(VDatum,AnzMonate)

\@ Format

Rückgabe: Datum

Monate addieren mit Valutadatum:

Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet.
Ansonsten siehe ADDMONTHS.

Beispiel:
ADDMONTHS_VD(„30.01.2015“, 1)\@ "tt.MM.jjjj" ergibt 30.02.2015

ADDYEARS(Date,AmountYears)

\@ Format

Return: Date

Add years:
A certain number of years is added to the value of the variable date and output in a desired format.

Parameters:

Date See requirements above

AmountYears Integer

Format Has to be a valid syntax for date formatting

Example:
ADDYEARS(„31.01.2015“,10)\@ "tt.MM.jjjj" results in 31.01.2025

ADDYEARS_VD(VDatum,AnzJahre)

\@ Format

Rückgabe: Datum

Jahre addieren mit Valutadatum:

Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet.
Ansonsten siehe ADDYEARS.

Beispiel:
ADDMONTHS_VD(„29.02.2016“, 3)\@ "tt.MM.jjjj" ergibt ..29.02.2019

ADDWORKINGDAYS(Date,AmountDays,
Holidays,WorkingDays)

\@ Format

Return: Date

Add working days:
A certain number of working days is added to the value of the variable varDate and output in a desired format.

Parameters:

Date See requirements above

AmoutDays Amount of days, integer

Holidays Optionally, a list of public holidays can be specified, which are taken into account in the calculation. Syntax: dd.MM. Data separated by ";".

WorkingDays Working days per week. Values between 2 and 7, default is 5.

Or input of a bitmap, which indicates
"1" for a working day. "0" for a day off.
Examples:
"1111110" – Sunday is a day off. "1111100" – Saturday and Sunday are off.

Format Needs to have a valid syntax for date formatting

Example:
ADDWORKINGDAYS („01.01.2015“,5,"01.01.;6.1.;31.12.",5)\@ "tt.MM.jjjj"
results in 09.01.2015

ADDWORKINGDAYS_VD(VDatum,AnzTage,
Feiertage,Arbeitstage)

\@ Format

Rückgabe: Datum

Werktage addieren mit Valutadatum:

Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet.
Ansonsten siehe ADDWORKINGDAYS.

Beispiel:
ADDWORKINGDAYS_VD(„01.01.2015“,22,"01.01.;6.1.;31.12.",5)\@ "tt.MM.jjjj"
ergibt 03.02.2015

GETNEXTWORKINGDAY(Date,Holidays,WorkingDays)

\@ Format

Return: Date

Next working day from date:

Based on the value of the variable varDate, the next working day is determined taking the days off into account and output in the desired format.

Parameters:

Date See requirements above.

Holidays Optionally, a list of public holidays can be specified, which are taken into account in the calculation. Syntax: dd.MM. Data separated by ";".

Format Needs to have a valid syntax for date formatting

Example:
GETNEXTWORKINGDAY („01.01.2015“,"01.01.;6.1.;31.12.",5)\@ "tt.MM.jjjj"
results in 02.01.2015

SUBTRACTDAYS(Date,AmountDays)

\@ Format

Return: Date

Subtract days:

A certain number of days is subtracted from the value of the Date variable and displayed in the desired format.

Parameters:

Date See requirement above

AmountDays Integer

Format Has to be a valid syntax for date formatting

Example:
SUBTRACTDAYS(„15.02.2015“,14)\@ "tt.MM.jjjj" results in 01.02.2015

SUBTRACTDAYS_VD(VDatum,AnzTage)

\@ Format

Rückgabe: Datum

Tage subtrahieren mit Valutadatum:

Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet.
Ansonsten siehe SUBTRACTDAYS.

Beispiel:
SUBTRACTDAYS_VD(„01.03.2015“, 1)\@ "tt.MM.jjjj" ergibt 30.02.2015

SUBTRACTMONTHS(Date,AmountMonth)

\@ Format

Return: Date

Subtract months:

A certain number of months is subtracted from the value of the variable Date and displayed in the desired format.

Parameters:

Date See requirement above

AmountMonth Integer

Format Has to be a valid syntax for date formatting

Example:
SUBTRACTMONTHS(„31.03.2015“,1)\@ "tt.MM.jjjj" results in 28.02.2015

SUBTRACTMONTHS_VD(VDatum,AnzMonate)

\@ Format

Rückgabe: Datum

Monate subtrahieren mit Valutadatum:

Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet.
Ansonsten siehe SUBTRACTMONTHS.

Beispiel:
SUBTRACTMONTHS_VD(„30.03.2015“, 1)\@ "tt.MM.jjjj" ergibt 30.02.2015

SUBTRACTYEARS(Date,AmountYears)

\@ Format

Return: Date

Subtract years:

A certain number of years is subtracted from the value of the variable Date and output in the desired format.

Parameters:

Date See requirement above

AmountYears Integer

Format Has to be a valid syntax for date formatting

Example:
SUBTRACTYEARS(„31.01.2015“,10)\@ "tt.MM.jjjj" results in 31.01.2005

SUBTRACTYEARS_VD(VDatum,AnzJahre)

\@ Format

Rückgabe: Datum

Jahre subtrahieren mit Valutadatum:

Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet.
Ansonsten siehe SUBTRACTYEARS.

Beispiel:
SUBTRACTYEARS_VD(„29.02.2016“, 3)\@ "tt.MM.jjjj" ergibt 29.02.2013

SUBTRACTWORKINGDAYS(Date,AmountDays,Holidays,WorkingDays)

\@ Format

Return: Date

Subtract working days:

A certain number of working days is subtracted from the value of the variable varDate and displayed in the desired format.

Parameters:

Date See requirements above

AmountDays Amount of working days, integer

Holidays Optionally, a list of public holidays can be specified, which are taken into account in the calculation. Syntax: dd.MM. Data separated by ";". Optionally, a list of public holidays can be specified, which are taken into account in the calculation. Syntax: dd.MM. Data separated by ";".

WorkingDays Working days per week. Value between 2 and 7, default is 5.
Oder input of a bitmap, which indicates
"1" for a working day. "0" for a day off.
Examples:
"1111110" – Sunday is a day off. "1111100" – Saturday and Sunday are off.

Format Has to be a valid syntax for date formatting

Example:
SUBTRACTWORKINGDAYS („08.01.2015“,3,"01.01.;6.1.;31.12.",5)\@ "tt.MM.jjjj"
results in 02.01.2015

DATEDIF(Date1,Date2,Unit)

Return: Integer

Calculate date difference:

The difference between the date values Date1 and Date2 is calculated. The time unit is controlled by the Unit parameter.

Parameters:

Date1
Date2 See requirements above

Unit Possible values:

Y Difference in completed years

M Difference in completed months

D Difference in days

MD Difference between the days (in days, ignoring month and year specification)

YM Difference between the months (in months, ignoring day and year specification)

YD Difference between days and months (in days, ignoring year specification)

Example:
DATEDIF("28.02.2015","01.03.2015",“Y“) results in 0

DATEDIF_VD(VDatum1,VDatum2,Einheit)

Rückgabe: Ganzzahl

Datumsdifferenz berechnen mit Valutadatum:

Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet.
Ansonsten siehe DATEDIF.

Beispiel:
DATEDIF_VD("30.02.2015","03.03.2015",“Y“) ergibt 0

GETDAYDIFF(Date1,Date2)

Return: Integer

Calculate difference in days:

The difference between the date values Date1 and Date2 is calculated in days.

Parameters:

Date1
Date2 See requirements above
Example:
GETDAYDIFF("28.02.2015","01.03.2015") results in 1

GETDAYDIFF_VD(VDatum1,VDatum2)

Rückgabe: Ganzzahl

Differenz in Tagen berechnen mit Valutadatum:

Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet.
Ansonsten siehe GETDAYDIFF.

Beispiel:
GETDAYDIFF_VD("28.02.2015","01.03.2015")
ergibt 3

GETMONTHDIFF(Date1,Date2)

Return: Integer

Calculate difference in months:

The difference between the date values Date1 and Date2 is calculated in months.

Parameters:

Date1
Date2 See requirements above
Example:
GETMONTHDIFF("01.01.2015","31.01.2015") results in 0

GETMONTHDIFF_VD(VDatum1,VDatum2)

Rückgabe: Ganzzahl

Differenz in Monaten berechnen mit Valutadatum:

Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet.
Ansonsten siehe GETMONTHDIFF.

Beispiel:
GETMONTHDIFF_VD("30.01.2015","30.02.2015") ergibt 1

GETYEARDIFF(Date1,Date2)

Return: Integer

Calculate difference in years:

The difference between the date values Date1 and Date2 is calculated in years.

Parameters:

Date1
Date2 See requirements above
Example:
GETYEARDIFF("31.12.2014","31.01.2015") results in 0

GETYEARDIFF_VD(VDatum1,VDatum2)

Rückgabe: Ganzzahl

Differenz in Jahren berechnen mit Valutadatum:

Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet.
Ansonsten siehe GETYEARDIFF.

Beispiel:
GETYEARDIFF_VD("31.12.2014","30.02.2015") ergibt 0

GETFIRSTOFMONTH(Date)

\@ Format

Return: Date

First day of the month:

The first day of the month of a given date is output in the desired format.

Parameter:

Date See requirements above

Format Has to be a valid syntax for date formatting

Example:
GETFIRSTOFMONTH(„23.02.2015“)\@ "tt.MM.jjjj" results in 01.02.2015

GETFIRSTOFMONTH_VD(VDatum)

\@ Format

Rückgabe: Datum

Erster Tag im Monat berechnen mit Valutadatum:

Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet.
Ansonsten siehe GETFIRSTOFMONTH.

Beispiel:
GETFIRSTOFMONTH_VD(„30.02.2015“)\@ "tt.MM.jjjj" ergibt 01.02.2015

GETFIRSTOFNEXTMONTH(Date)

\@ Format

Return: Date

First day of next month:

The first day of the next month of a given date is output in the desired format.

Parameters:

Date See requirements above

Format Has to be a valid syntax for date formatting

Example:
GETFIRSTOFNEXTMONTH(„23.02.2015“)\@ "tt.MM.jjjj" results in 01.03.2015

GETFIRSTOFNEXTMONTH_VD(VDatum)

\@ Format

Rückgabe: Datum

Erster Tag des nächsten Monats berechnen mit Valutadatum:

Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet.
Ansonsten siehe GETFIRSTOFNEXTMONTH.

Beispiel:
GETFIRSTOFNEXTMONTH_VD(„30.02.2015“)\@ "tt.MM.jjjj" ergibt 01.03.2015

GETFIRSTOFQUARTER(Date)

\@ Format

Return: Date

First day of the quarter:

The first day of the quarter of a given date is output in the desired format.

Parameters:

Date See requirements above

Format Has to be a valid syntax for date formatting

Example:
GETFIRSTOFQUARTER(„23.02.2015“)\@ "tt.MM.jjjj" results in 01.01.2015

GETFIRSTOFQUARTER_VD(VDatum)

\@ Format

Rückgabe: Datum

Erster Tag des Quartals berechnen mit Valutadatum:

Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet.
Ansonsten siehe GETFIRSTOFQUARTER.

Beispiel:
GETFIRSTOFQUARTER_VD(„30.02.2015“)\@ "tt.MM.jjjj" ergibt 01.01.2015

GETLASTOFMONTH(Date)

\@ Format

Return: Date

Last day of the month:

The last day of the month of a given date is output in the desired format.

Parameters:

Date See requirements above

Format Has to be a valid syntax for date formatting

Example:
GETLASTOFMONTH(„23.02.2015“)\@ "tt.MM.jjjj" results in 28.02.2015

GETLASTOFMONTH_VD(VDatum)

\@ Format

Rückgabe: Datum

Letzter Tag des Monats berechnen mit Valutadatum:

Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet.
Ansonsten siehe GETLASTOFMONTH.

Beispiel:
GETLASTOFMONTH_VD(„23.02.2015“)\@ "tt.MM.jjjj" ergibt 30.02.2015

GETLASTOFLASTMONTH(Date)

\@ Format

Return: Date

Last day of last month:

The last day of the previous month of a specified date is output in the desired format.

Parameters:

Date See requirements above

Format Has to be a valid syntax for date formatting

Example:
GETLASTOFLASTMONTH(„23.02.2015“)\@ "tt.MM.jjjj" results in 31.01.2015

GETLASTOFLASTMONTH_VD(VDatum)

\@ Format

Rückgabe: Datum

Letzter Tag des letzten Monats berechnen mit Valutadatum:

Datumsvariablen vom Typ Valutadatum werden immer mit 30 Tagen im Monat berechnet.
Ansonsten siehe GETLASTOFLASTMONTH.

Beispiel:
GETLASTOFLASTMONTH_VD(„23.03.2015“)\@ "tt.MM.jjjj" ergibt 30.02.2015

GETLASTOFQUARTER(Date)

\@ Format

Return: Date

Last day of the quarter:

The last day of the quarter of a specified date is output in the desired format.

Parameters:

Date See requirements above

Format Has to be a valid syntax for date formatting

Example:
GETLASTOFQUARTER(„23.02.2015“)\@ "tt.MM.jjjj" results in 31.03.2015

Rules for processing text and date functions

Processing variables from the data supply

Within a text or date function the name of a variable from the data supply can be used to process the content of this variable.

Example1:

{ SET myVar " My content from the data supply " }

{= LEFT(myVar; 4) }
Result: My

The content of the variable myVar may contain any characters, including special characters such as quotation marks, semicolons, commas, brackets, etc.

Example2:

To be able to correctly process special characters that would otherwise be interpreted by RTF processing, the following procedure is recommended:

{ SET COMMA "," }

{ = SUBSTITUTE("123.45"; "."; COMMA; "1") }

Result: 123,45

The special character is assigned to a variable by the SET command, which can then be used in the SUBSTITUTE command.

Example3:

{ SET myDate "15.02.2015" }

{= ADDDAYS(myDate, 14)\@ "tt.MM.jjjj }
Result: 01.03.2015

Interlinked processing of text and date functions

To process text or date functions concatenated, i.e. to continue working within a text/date function with the result of another text/date function, the detour via variables is necessary.

Example:

{SET a " abc "}

{SET b { = TRIM (a)} }

{ = LEFT(b;2)}

Result: ab

Here b is first assigned the value abc (the blanks are removed by the TRIM function). Then b is passed to the LEFT function.