Format Strings

Format strings are used to control how data is displayed on spreadsheets and forms. They are used when defining fields in a table and when defining controls on forms.

For example, you might want to display the telephone number 8005551212 as (800) 555-1212. To do this, use the format string (###) ###-####. Or you might want to format the date 6/30/96 as Friday, June 30, 1996. To do this, use the format string Dddd, Mmmm d, yyyy.

The following sections provide details about format strings for text, numbers, and dates.

Format Strings for Text

Text data can be formatted for password style entry in which asterisks (*) are displayed instead of the actual text. This is useful for entering password or other sensitive information.

To set up password formatting for a field in a table, select Password for the Format attribute or enter an asterisk (*).

To set up password formatting for a control on a form, select the control and enter an asterisk (*) in the Format attribute in the Attribute window.

When Enterprise Data Manager displays a field with password formatting, asterisks are displayed instead of the actual data. When the user enters data into a password formatted field, Enterprise Data Manager displays asterisks as each character is typed.

Format Strings for Numbers

Format strings allow you to format numeric values with dollar signs, thousand separators, scientific notation, percents, etc. You can format positive numbers and negative numbers differently. You can enter format strings for table columns and form controls when you are designing them.

Numeric format strings can have one or two sections, separated by a semicolon. If the format string has one section, then positive and negative values use the same format. A negative sign (-) is automatically inserted for negative number. If there are two sections, the first section is for positive numbers and the second for negative numbers.

Format Strings for Numbers

Format string	Value	Formatted value
0.00	100.5	100.50
	-145.1	-145.10
0.00;(0.00)	100.5	100.50
	-145.1	(145.10)

Symbols Allowed in a Numeric Format String

Symbol	Description
$	Output the currency string. The currency string is specified in the country setting for your system.
.	Output the decimal point character. The decimal point character is specified in the country settings for your system.
,	Output the thousands separator character. The thousands separator character is specified in the country settings for your system.
#	Output a digit. If there is no digit to output in the position, output nothing. Value Format String Displayed Value 12.3 ### ## 12.3 125.224 ### ## 125.22 0 ### ## . 1500 ### ## 1500. Note: If the value has more digits to the left of the decimal than there are symbols in the format string, the format string is automatically extended to the left. However, if the value has more digits to the right of the decimal point than appear in the format string, the value is rounded to the last digit.
0	Output a digit. If there is no digit to output in the position, output a zero. Value Format String Displayed Value 12.3 000.00 012.30 125.224 000.00 125.22 0 000.00 000.00 1500 000.00 1500.00
?	Output a digit. If there is no digit to output in the position, output a space character. Note: If the value has more digits to the left of the decimal than there are symbols in the format string, the format string is automatically extended to the left. However, if the value has more digits to the right of the decimal point than appear in the format string, the value is rounded to the last digit. Value Format String Displayed Value 12.3 ???.?? "12.3" 125.224 ???.?? "125.22" 0 ???.?? " . " 1500 ???.?? "1500. "
%	Output the value as a percentage. The value is multiplied by 100 and the percent character (%) is output. Value Format String Displayed Value 0.15 #0% 15% 0.04 #0% 4% 0 #0% 0% 1 #0% 100%
e+ e-	Output using scientific notation. e+ outputs the sign of the exponent only if it is negative, e- always outputs the sign of the exponent. Value Format String Displayed Value 12500 0.00e+#0 1.25e04 0.005 0.00e+#0 5.0e-03 12500 0.00e-#0 1.25e+04 0.005 0.00e-#0 5.0e-03 Note: You can also use E+ or E- in the format string. This causes the "E" to be uppercase in the formatted value.
-+()space	Output plus or minus signs, parentheses, or blank spaces. These characters are often used to distinguish positive and negative values. Value Format String Displayed Value 12.3 +0.00;-0.00 +12.30 -1.1 +0.00;-0.00 -1.10 12.3 0. 0 0 12. 3 0 Note: These are the only characters that can be included in numeric format strings to be output directly. To output other characters or strings, use the "\" symbol or enclose the characters in quotation marks.
\	Output the character following the backslash. For example, if the format string is "0.00 \t\o\n\s", the value 1.25 is formatted as "1.25 tons".
"string"	Output the string. The quotation marks are not output. For example, if the format string is "0.00 "tons"", the value 1.25 is formatted as "1.25 tons".
'string'	Output the string. The quotation marks are not output. For example, if the format string is "0.00 'tons'", the value 1.25 is formatted as "1.25 tons".
GN	General format for numbers. This is the format used if no format string is given. Value Format String Displayed Value 12.3 GN 12.3 125.224 GN 125.224 0 GN 0 -1500 GN -1500 Note: If you use GN, the only other symbols you can use in the format string are those enclosed in brackets; for example [US].
GF	General fixed format for number. This is the same as the GN format except that the number of decimal digits specified in the country settings for your system is assumed. Note: If you use GF, the only other symbols you can use in the format string are those enclosed in brackets; for example [US].
GC	General currency format for numbers. The currency format in the country setting for your system is used. Note: If you use GC, the only other symbols you can use in the format string are those enclosed in brackets; for example [US].
[S/n]	Scale the number before it is output. The number is divided by 'n' before it is formatted. 'n' must be a power of 10 (10, 100, 1000, etc.). For example, if the format string is "#0.00[S/1000]", 12340 is formatted as "12.34".
[US]	The information specified in the country setting of your system is ignored. Instead, the United States defaults are substituted (periods for decimal points, commas for thousand separators, and $ for the currency symbol). For example, if the format string is "$#,##0.00[US]", 1234.56 is formatted as "$1,234.56", regardless of the country settings of your system.
*	Password format. Asterisks (*) will be displayed instead of the actual data.

Format Strings for Dates and Times

Date/time formats allow you to control which parts of the date or time are to be output, their order, and whether to spell out months and days. You can enter format strings for table columns, and form controls when you are designing them.

When editing dates and times on forms or spreadsheets, the format string controls the string that is displayed during editing. For example, if the format string contains date and time symbols (i.e. 'GDT'), when you edit the field, the data will be presented in the form 'M/d/yy h:mm:ss a' (country dependent). If the format string only contains date symbols (i.e. 'GD'), when you edit the field, you will only see the date part 'M/d/yy' and the time will be set to 12:00AM. If the format string only contains time symbols (i.e. 'GT'), when you edit the field, you will only see the time part 'h:mm:ss a' and the date will be set to 1899-12-30.

The following table describes the symbols allowed in a date/time format string.

Symbols Allowed in Date/Time Format Strings

Date and time formats are specified by date and time pattern strings. Within date and time pattern strings, unquoted letters from 'A' to 'Z' and from 'a' to 'z' are interpreted as pattern letters representing the components of a date or time string. Text can be quoted using single quotes (') to avoid interpretation. "''" represents a single quote. All other characters are not interpreted; they're simply copied into the output string during formatting or matched against the input string during parsing.

The following pattern letters are defined (all other characters from 'A' to 'Z' and from 'a' to 'z' are reserved):

Letter	Date or Time Component	Presentation	Examples
G	Era designator	Text	AD
y	Year	Year	1996; 96
M	Month in year	Month	July; Jul; 07
w	Week in year	Number	27
W	Week in month	Number	2
D	Day in year	Number	189
d	Day in month	Number	10
F	Day of week in month	Number	2
E	Day in week	Text	Tuesday; Tue
a	Am/pm marker	Text	PM
H	Hour in day (0-23)	Number	0
k	Hour in day (1-24)	Number	24
K	Hour in am/pm (0-11)	Number	0
h	Hour in am/pm (1-12)	Number	12
m	Minute in hour	Number	30
s	Second in minute	Number	55
S	Millisecond	Number	978
z	Time zone	General time zone	Pacific Standard Time; PST; GMT-08:00
Z	Time zone	RFC 822 time zone	-0800

• Text: For formatting, if the number of pattern letters is 4 or more, the full form is used; otherwise a short or abbreviated form is used if available. For parsing, both forms are accepted, independent of the number of pattern letters.
• Number: For formatting, the number of pattern letters is the minimum number of digits, and shorter numbers are zero-padded to this amount. For parsing, the number of pattern letters is ignored unless it's needed to separate two adjacent fields.
• Year: For formatting, if the number of pattern letters is 2, the year is truncated to 2 digits; otherwise it is interpreted as a number.

  For parsing, if the number of pattern letters is more than 2, the year is interpreted literally, regardless of the number of digits. So using the pattern "MM/dd/yyyy", "01/11/12" parses to Jan 11, 12 A.D.

  For parsing with the abbreviated year pattern ("y" or "yy"), SimpleDateFormat must interpret the abbreviated year relative to some century. It does this by adjusting dates to be within 80 years before and 20 years after the time the SimpleDateFormat instance is created. For example, using a pattern of "MM/dd/yy" and a SimpleDateFormat instance created on Jan 1, 1997, the string "01/11/12" would be interpreted as Jan 11, 2012 while the string "05/04/64" would be interpreted as May 4, 1964. During parsing, only strings consisting of exactly two digits, as defined by Character.isDigit(char), will be parsed into the default century. Any other numeric string, such as a one digit string, a three or more digit string, or a two digit string that isn't all digits (for example, "-1"), is interpreted literally. So "01/02/3" or "01/02/003" are parsed, using the same pattern, as Jan 2, 3 AD. Likewise, "01/02/-3" is parsed as Jan 2, 4 BC.

• Month: If the number of pattern letters is 3 or more, the month is interpreted as text; otherwise, it is interpreted as a number.
• General time zone: Time zones are interpreted as text if they have names. For time zones representing a GMT offset value, the following syntax is used:

  	 GMTOffsetTimeZone:
  	 GMT Sign Hours : Minutes
  	
  	 Sign: one of
  	 + -
  	 Hours:
  	 Digit
  	 Digit Digit
  	
  	 Minutes:
  	 Digit Digit
  	 Digit: one of
  	 0 1 2 3 4 5 6 7 8 9

  Hours must be between 0 and 23, and Minutes must be between 00 and 59. The format is locale independent. TwoDigitHours must be between 00 and 23. Other definitions are as for general time zones.

  For parsing, general time zones are also accepted.

Examples

Date and Time Pattern	Result
"yyyy.MM.dd G 'at' HH:mm:ss z"	2001.07.04 AD at 12:08:56 PDT
"EEE, MMM d, ''yy"	Wed, Jul 4, '01
"h:mm a"	12:08 PM
"hh 'o''clock' a, zzzz"	12 o'clock PM, Pacific Daylight Time
"K:mm a, z"	0:08 PM, PDT
"yyyyy.MMMMM.dd GGG hh:mm aaa"	02001.July.04 AD 12:08 PM
"EEE, d MMM yyyy HH:mm:ss Z"	Wed, 4 Jul 2001 12:08:56 -0700
"yyMMddHHmmssZ"	010704120856-0700
"yyyy-MM-dd'T'HH:mm:ss.SSSZ"	2001-07-04T12:08:56.235-0700

The following format strings may be used to refer to dates, times, or date/time values in the default format of the current locale.

Symbol	Description
GD	General date format. This format is used if no format string is given. The short date format in the country settings for your system is used.
GT	General format for time. The time format in the country settings for your system is used.
GDT	General format for dates with times. The time format in the country settings for your system is appended to the short date format.