Creating reports
3. Click Format > Numbers > Custom and select the format from the list
5.2.4.1.2 Custom formats
You can use the Custom format type to define a customized format for any cell.
Note:
You cannot apply custom formats to numbers in tables in the Web interface (DHTML interface).
The following table lists the strings you can use to create custom formats:
Example Display(s)
Character(s)
‘12345’ with the format #,##0 gives
‘12,345’ (if your locale defines the grouping separator as a comma) or
‘12 345’ (if your locale defines the grouping separator as a space) The corresponding digit. If the
number has less digits than the number of # characters used to specify the format, no leading zeros are inserted.
#
‘123’ with the format #0,000 gives
‘0,123’
The corresponding digit. If the number has less digits than the number of 0 characters used to specify the format, a leading zero(s) is inserted before the number.
0
‘1234567’ with the format #,##0 gives ‘1,234,567’ (if you locale de-fines the grouping separator as a comma) or ‘1 234 567’ (if your locale defines the grouping separator as a non-breaking space)
The grouping separator as de-fined by your locale.
,
‘12.34’ with the format #.#0 gives
‘12.34’ (if your locale defines the decimal separator as a period) or
‘12,34’ (if your locale defines the decimal separator as a comma) The decimal separator as
de-fined by your locale.
.
0.50 becomes 50%.
Displays a percentage sign (%) after the result and multiplies the result by 100.
[%]%
0.50 becomes 0.50%
The % sign after the result, but does not multiply the result by 100.
%
‘1234567’ with the format # ##0 gives ‘1234 567’
A non-breaking space ( )
Example Display(s)
Character(s)
‘705.15’ with the format $#.#0 gives
‘$705.15’ or with the format #,#0 € gives ‘705,15 €’
The alphanumeric character.
1, 2, 3, a, b, c, $,
£, €(and so on)
‘150’ with the format #,##0[Red]
gives ‘150’ #,##0[Blue] gives ’150’
The value in the specified color.
[Red], [Blue],
The first day of a month with the for-mat d gives ‘1’
The number of the day in the month with no leading zeros. If the date for day is less than two characters, the date displays without a zero before it.
d
The first day of a month with the for-mat dd gives ‘01’
The number of the day with leading zeros. If the date for day is less than two characters, the date displays with a zero before it.
dd
'Monday’ with the format ddd gives
‘Mon’ in English, in French, lundi gives lun.
The name of the day abbreviat-ed. The first letter is capitalized if the selected locale uses capi-talized day names.
ddd
'Monday’ with the format Dddd gives
‘Mon’ in English, in French, lundi gives Lun.
Forced the capitalization of the day name, for any locale.
Dddd
‘Monday’ with the format dddd gives
‘Monday’ in English. In French, the day is lundi.
The name of the day in full. The first letter is capitalized if the selected locale uses capitalized day names.
dddd
‘Monday’ with the format DDDD gives
‘MONDAY’ in English. In French, the day is LUNDI.
The name of the day in full, in uppercase.
DDDD
‘Monday’ with the format dddd dd gives ‘Monday 01’
The day of the week followed by a space and the number of the day.
dddd dd
(month, year) Calendar characters
Example Display(s)
Character(s)
‘January’ with the format M gives '1' The number of the month with
no leading zeros. If the number for month is less than two char-acters, the number displays without a zero before it.
M
‘January’ with the format MM gives 01 The number of the month with
leading zeros. If the number for month is less than two charac-ters, the number displays with a zero before it.
MM
‘January’ with the format mmm gives Jan in English. In French, this is 'jan'.
The name of the month abbrevi-ated. The first letter is capital-ized if the selected locale uses capitalization.
mmm
‘January’ with the format mmm gives Jan in English. In French, this is 'Jan'.
The name of the month abbrevi-ated. The first letter is capital-ized for all locales.
Mmmm
‘January’ with the format mmmm gives January in English, janvier in French The name of the month in full.
The first letter is capitalized if the selected locale used capital-ization.
mmmm
‘January’ with the format MMMM gives JANUARY in English, JANVIER in French
The name of the month in full all in uppercase.
MMMM
‘2003’ with the format yy gives ‘03’
The last two digits for year.
yy
‘2003’ with the format yyyy gives
‘2003’
All four digits for year.
yyyy
(hours, minutes, sec onds, am/pm)
Time of day charac ters
‘21:05:03’ with the format hh:mm:ss agives ‘9:05:03 PM’ for English lo-cale
The hour with no leading zeros and the minutes and seconds with leading zeros. The “a"
character displays AM or PM after the time when available.
hh:mm:ss a
‘21:00’ with the format H gives ‘21’.
Possible values are 0-23.
The hour according to the 24-hour clock, starting at 0. No leading zero for single figure hours.
H
Example Display(s)
Character(s)
‘21:00’ with the format HH gives ‘21’.
Possible values are 00-23.
The hour according to the 24-hour clock, starting at 0.
HH
‘21:00’ with the format k gives ‘21’.
Possible values are 1-24.
The hour according to the 24-hour clock, starting at 1. No leading zero for single figure hours.
k
‘21:00’ with the format kk gives ‘21’.
Possible values are 01-24.
The hour according to the 24-hour clock, starting at 01.
kk
‘21:00’ with the format hh gives ‘09’
The hour according to the 12-hour clock.
hh
‘7.15 am’ with the format HH:mm gives ‘07:15’
The hour and minutes with leading zeros.
HH:mm
‘7.15 am’ with the format HH:mm:ss gives ‘07:15:00’
The hour, minutes, and seconds with leading zeros.
HH:mm:ss
‘07:15:03’ with the format mm:ss gives ‘15:03’
The minutes, and seconds with leading zeros.
mm:ss
To define a custom format