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.