Custom formats

To format numbers, you can customize the Format attribute for individual fields. Make sure that the Design Mode option is activated on the Avelon Reporting tab, because otherwise the fields cannot be edited.

../../_images/format-attribute.png

To specify a custom number format, you must specify wildcards to define the appearance of the formatted value. Important placeholders, also known as number format identifiers, can be found in the following sections.

Note

The format identifiers in the next sections are only available in Microsoft Word and Microsoft PowerPoint. In Microsoft Excel, please use the Format Cells function to format cells as usual.

Numbers

The following table describes the custom number format identifiers and displays an example output as created by each format identifier.

Numbers

Format identifier

Name

Description

Examples

“0”

0 placeholder

Replaces the digit 0 with a corresponding existing digit if necessary; otherwise, the digit 0 is displayed in the result string.
1234.5678 (“00000”) → 01235
0.45678 (“0.00”, en-US) → 0.46
0.45678 (“0.00”, fr-FR) → 0,46

“#”

Digit placeholder

Replaces the “#” symbol with a corresponding existing digit if necessary; otherwise, no digit is displayed in the result string. Note that no digit is displayed in the result string if the corresponding digit in the input string is a non-significant 0. For example: 0003 (“####”) → 3.
1234.5678 (“#####”) → 1235
0.45678 (“#.##”, en-US) → .46
0.45678 (“#.##”, fr-FR) → ,46

“.”

Decimal separator

Determines the position of the decimal separator in the result string.
0.45678 (“0.00”, en-US) → 0.46
0.45678 (“0.00”, fr-FR) → 0,46

“,”

Group separators and number scaling

The character is used both as an identifier for group separators and as an identifier for number scaling. When used as an identifier for group separators, a localized separator is inserted between the individual groups. When used as an identifier for number scaling, a number is divided by 1000 for each character specified.
Identifier for group separators:
2147483647 (“##,#”, en-US) → 2,147,483,647
2147483647 (“##,#”, es-ES) → 2.147.483.647
Identifier for number scaling:
2147483647 (“#,#,,”, en-US) → 2,147
2147483647 (“#,#,,”, es-ES) → 2.147

“%”

Percentage placeholder

Multiplies a number by 100 and inserts a localized percent symbol into the result string.
0.3697 (“%#0.00”, en-US) → %36.97
0.3697 (“%#0.00”, el-GR) → %36,97
0.3697 (“##.0 %”, en-US) → 37.0 %
0.3697 (“##.0 %”, el-GR) → 37,0 %

“‰”

Per mill placeholder

Multiplies a number by 1000 and inserts a localized per mill symbol into the result string.
0.03697 (“#0.00‰”, en-US) → 36.97‰
0.03697 (“#0.00‰”, ru-RU) → 36,97‰
“E0”
“E+0”
“E-0”
“e0”
“e+0”
“e-0”

Exponential notation

Formats the result with exponential notation if at least one 0 (zero) follows. The upper or lower case (“E” or “e”) specifies the spelling of the symbol for the exponent in the result string. The number of zeros following the character “E” or the character “e” determines the minimum number of digits in the exponent. A plus sign (+) indicates that the exponent is always preceded by a sign. A minus sign (-) indicates that only negative exponents are preceded by a sign.
987654 (“#0.0e0”) → 98.8e4
1503.92311 (“0.0##e+00”) → 1.504e+03
1.8901385E-16 (“0.0e+00”) → 1.9e-16

\

Escape character

The character that follows the escape character is interpreted as a literal and not as a custom format identifier.
987654 (”###00#”) → #987654#
‘String’
“String”

String literal separator

Specifies that the enclosed characters are to be copied unchanged into the result string.
68 (”# ‘ degrees’”) → 68 degrees
68 (“#’ degrees’”) → 68 degrees

;

Section separator

Defines sections with separate format strings for positive and negative numbers and zeros.
12.345 (“#0.0#;(#0.0#);-0-”) → 12.35
0 (“#0.0#;(#0.0#);-0-”) → -0-
-12.345 (“#0.0#;(#0.0#);-0-”) → (12.35)
12.345 (“#0.0#;(#0.0#)”) → 12.35
0 (“#0.0#;(#0.0#)”) → 0.0
-12.345 (“#0.0#;(#0.0#)”) → (12.35)

Other

All other characters

The character is copied unchanged into the result string.

68 (”# °”) → 68 °

Date and time

The following table describes the custom date and time format identifiers and shows a result string as created by each individual format identifier. By default, result strings correspond to the formatting conventions of the “en-US” locale. When a particular format identifier generates a localized result string, the example also specifies the locale for which the result string applies.

Date and time

Format identifier

Description

Examples

“d”

The day of the month, from 1 to 31.
2009-06-01T13:45:30 → 1
2009-06-15T13:45:30 → 15

“dd”

The day of the month, from 01 to 31.
2009-06-01T13:45:30 → 01
2009-06-15T13:45:30 → 15

“ddd”

The abbreviated name of the day of the week.
2009-06-15T13:45:30 → Mo. (de-DE)
2009-06-15T13:45:30 → Пн (ru-RU)
2009-06-15T13:45:30 → lun. (fr-FR)

“dddd”

The full name of the day of the week.
2009-06-15T13:45:30 → Montag (de-DE)
2009-06-15T13:45:30 → понедельник (ru-RU)
2009-06-15T13:45:30 → lundi (fr-FR)

“f”

The tenth of a second in a date and time value.
2009-06-15T13:45:30.6170000 → 6
2009-06-15T13:45:30.05 → 0

“ff”

The hundredth of a second in a date and time value.
2009-06-15T13:45:30.6170000 → 61
2009-06-00-15T13:45:30.0050000 >

“fff”

The milliseconds in a date and time value.
6/15/2009 13:45:30.617 → 617
6/15/2009 13:45:30.0005 → 000

“ffff”

The ten thousandth of a second in a date and time value.
2009-06-15T13:45:30.6175000 → 6175
2009-06-15T13:45:30.0000500 → 0000

“fffff”

The hundred thousandth of a second in a date and time value.
2009-06-15T13:45:30.6175400 → 61754
6/15/2009 13:45:30.000005 → 00000

“ffffff”

The millionth of a second (nanosecond) in a date and time value.
2009-06-15T13:45:30.6175420 → 617542
2009-06-15T13:45:30.0000005 → 000000

“fffffff”

The ten millionth of a second in a date and time value.
2009-06-15T13:45:30.6175425 → 6175425
2009-06-15T13:45:30.0001150 → 0001150

“F”

If unequal to 0 (zero), the tenth of a second in a date and time value.
2009-06-15T13:45:30.6170000 → 6
2009-06-15T13:45:30.0500000 → (no output)

“FF”

If unequal to 0 (zero), the hundredth of a second in a date and time value.
2009-06-15T13:45:30.6170000 → 61
2009-06-15T13:45:30.0050000 → (no output)

“FFF”

If unequal to 0 (zero), the milliseconds in a date and time value.
2009-06-15T13:45:30.6170000 → 617
2009-06-15T13:45:30.0005000 → (no output)

“FFFF”

If unequal to 0 (zero), the ten thousandth of a second in a date and time value.
2009-06-15T13:45:30.5275000 → 5275
2009-06-15T13:45:30.0000500 → (no output)

“FFFFF”

If unequal to 0 (zero), the hundred thousandth of a second in a date and time value.
2009-06-15T13:45:30.6175400 → 61754
2009-06-15T13:45:30.0000050 → (no output)

“FFFFFF”

If unequal to 0 (zero), the millionth of a second (nanosecond) in a date and time value.
2009-06-15T13:45:30.6175420 → 617542
2009-06-15T13:45:30.0000005 → (no output)

“FFFFFFF”

If unequal to 0 (zero), the ten millionth of a second in a date and time value.
2009-06-15T13:45:30.6175425 → 6175425
2009-06-15T13:45:30.0001150 → 000115

“g”, “gg”

The time period.
2009-06-15T13:45:30.6170000 → A.D.

“h”

The hour, from 1 to 12 (12-hour format).
2009-06-15T01:45:30 → 1
2009-06-15T13:45:30 → 1

“hh”

The hour, from 01 to 12 (12-hour format).
2009-06-15T01:45:30 → 01
2009-06-15T13:45:30 → 01

“H”

The hour, from 0 to 24 (24-hour format).
2009-06-15T01:45:30 → 1
2009-06-15T13:45:30 → 13

“HH”

The hour, from 00 to 23 (24-hour format).
2009-06-15T01:45:30 → 01
2009-06-15T13:45:30 → 13

“K”

Time zone information.
With DateTime values:
2009-06-15T13:45:30, type not specified →
2009-06-15T13:45:30, UTC time zone → Z
2009-06-15T13:45:30, local time zone → -07:00 (depends on the local computer settings)
With DateTimeOffset values:
2009-06-15T01:45:30-07:00 → -07:00
2009-06-15T08:45:30+00:00 → +00:00

“m”

The minutes, from 0 to 59.
2009-06-15T01:09:30 → 9
2009-06-15T13:29:30 → 29

“mm”

The minutes, from 00 to 59.
2009-06-15T01:09:30 → 09
2009-06-15T01:45:30 → 45

“M”

The month, from 1 to 12.
2009-06-15T13:45:30 → 6

“MM”

The month, from 01 to 12.
2009-06-15T13:45:30 → 06

“MMM”

The abbreviated name of the month.
2009-06-15T13:45:30 → Jun (de-DE)
2009-06-15T13:45:30 → juin (fr-FR)
2009-06-15T13:45:30 → Jun (zu-ZA)

“MMMM”

The full name of the month.
2009-06-15T13:45:30 → June (en-US)
2009-06-15T13:45:30 → juni (da-DK)
2009-06-15T13:45:30 → uJuni (zu-ZA)

“s”

The seconds, from 0 to 59.
2009-06-15T13:45:09 → 9

“ss”

The seconds, from 00 to 59.
2009-06-15T13:45:09 → 09

“t”

The first character of the AM/PM identifier.
2009-06-15T13:45:30 → P (en-US)
2009-06-15T13:45:30 → 午 (ja-JP)
2009-06-15T13:45:30 → (fr-FR)

“tt”

The AM/PM identifier.
2009-06-15T13:45:30 → PM (en-US)
2009-06-15T13:45:30 → 午後 (ja-JP)
2009-06-15T13:45:30 → (fr-FR)

“y”

The year, from 0 to 99.
0001-01-01T00:00:00 → 1
0900-01-01T00:00:00 → 0
1900-01-01T00:00:00 → 0
2009-06-15T13:45:30 → 9
2019-06-15T13:45:30 → 19

“yy”

The year, from 00 to 99.
0001-01-01T00:00:00 → 01
0900-01-01T00:00:00 → 00
1900-01-01T00:00:00 → 00
2019-06-15T13:45:30 → 19

“yyy”

The year, with a minimum of three digits.
0001-01-01T00:00:00 → 001
0900-01-01T00:00:00 → 900
1900-01-01T00:00:00 → 1900
2009-06-15T13:45:30 → 2009

“yyyy”

The year as a four-digit number.
0001-01-01T00:00:00 → 0001
0900-01-01T00:00:00 → 0900
1900-01-01T00:00:00 → 1900
2009-06-15T13:45:30 → 2009

“yyyyy”

The year as a five-digit number.
0001-01-01T00:00:00 → 00001
2009-06-15T13:45:30 → 02009

“z”

Offset from UTC in hours, without leading zeros.
2009-06-15T13:45:30-07:00 → -7

“zz”

Offset from UTC in hours, with one leading zero for a one-digit value.
2009-06-15T13:45:30-07:00 → -07

“zzz”

Offset from UTC in hours and minutes.
2009-06-15T13:45:30-07:00 → -07:00

“:”

The time separator.
2009-06-15T13:45:30 → : (en-US)
2009-06-15T13:45:30 → . (it-IT)
2009-06-15T13:45:30 → : (ja-JP)

“/”

The date separator.
2009-06-15T13:45:30 → / (en-US)
2009-06-15T13:45:30 → - (ar-DZ)
2009-06-15T13:45:30 → . (tr-TR)
“String”
‘String’

Separator for a literal string.
2009-06-15T13:45:30 (“arr:” h:m t) → arr: 1:45 P
2009-06-15T13:45:30 (‘arr:’ h:m t) → arr: 1:45 P

%

Defines the next character as a custom format identifier.
2009-06-15T13:45:30 (%h) → 1

\

The escape character.
2009-06-15T13:45:30 (h h) → 1 h

Any other character

The character is copied unchanged into the result string.

2009-06-15T01:45:30 (arr hh:mm t) → arr 01:45 A