Text / Date Functions

Text enters a constant text.

trim( Text ) eliminates spaces at the beginning and at the end of the Text.

lower( Text ) converts the Text into lower case letters.

upper( Text ) converts the Text into upper case letters.

lpad( Text, Number, Fill-in text ) creates a text that is at least Number characters long. If the Text is shorter than Number characters, the Fill-in text will be put in front so many times that the defined length is reached.

rpad( Text, Number, Fill-in text ) works like left pad, but puts the Fill-in text behind the Text.

Substring( Text, Start, End ) Extracts a part out of the Text from Start (inclusive) to End (exclusive). The count starts with 0, that means 0 represents the first character of the Text. Examples:

• substring( “Hallo World!”, 0, 5 ) returns “Hallo”.
• substring( “Hallo World!”, 6, 10 ) returns “World”.

format( Number, Format ) converts a Number into a formatted text. As Format a Text is needed:

• 0 represents a number or 0
• # represents a number that is diplayed only, if the number is big enough
• . represents the decimal separator (will be formatted according to the country, that means, in the UK and US it is shown as a floating point)
• , represents the grouping separator (will be formatted according to the country,that means, in the UK and US it is shown as a comma)

Examples:

• format(42.5, "0") results in "42"
• format(42.5, "000") results in "042"
• format(42.5, "000.00") results in "042.50"
• format(42.5, "0.00") results in "42.50"
• format(42.5, "#,##0.00") results in "42.50"
• format(1042.5, "#,##0.00") results in "1,042.50"

Date functions

The following functions can be applied to Appointments and Date / Time as well. For appointments the start date is used.

year( Date ) returns the year.

month( Date ) returns the months.

day( Date ) Returns the day of month.

weekday( Date ) Returns the weekday.

Year/Month( Date ) returns a text composed of year and month. This is useful for month-based analyses. Use this function to group a table by months.

Start( Appointment ) calculates the start time of the Appointment.

end( Appointment ) calculates the end time of the Appointment.

Duration( Appointment ) calculates the duration of the Appointment, applying the following formula:

Duration( Appointment ) = End( Appointment ) – Start( Appointment ).

age( Date ) calculates the current age (of a person) in years.

today returns the current date.