/ Layouts / Chapter 48. Tables / Formatting values

Data formats

Below you will find rules of formatting all types of data admissible for table cells.

Numeric format

The notion of a numeric format unites different representations of integer and fractional numbers. The following representations belong to this type:

• Integer representation. The value is displayed as integer number, real numbers are rounded to the nearest integer one. The following pattern characters can be used for digits:

  • 0 – if significant digit of number corresponds to the pattern, this digit is displayed, otherwise 0 is displayed (that is, the number is padded with zeros from the left);

  • ? – if significant digit of number corresponds to the pattern, this digit is displayed, otherwise a space is displayed (that is, the number is padded with spaces to the left);

  • # – if significant digit of number corresponds to the pattern, this digit is displayed, otherwise the pattern is ignored (that is, the number is aligned to the left);

• If the formatting string contains a space between patterns, all the digits to the left from this space are divided into groups of three digits separated by spaces.

• If the number of significant digits is more than the number of patterns in the string, the remaining digits are displayed to the left of the leftmost pattern.

• If there is the character “+” to the left of the leftmost pattern, the sign of the number will be displayed not only for negative numbers, but for positive numbers as well, and if the character “&” is present, then the sign will not be displayed at all even for negative numbers.

• Decimal representation. The value is displayed as a real number fractional part of which is separated from integer one using a decimal separator set in Windows (a point or a comma).

• The formatting string for decimal representation consists of two parts between which decimal separator is placed. The first substring determines formatting of integer part of the number, the second – the fractional one. Both substrings are made up of numeric patterns 0, ? and # enumerated above.

• Formatting of integer part of the number is performed according to the same rules which were described above for integer representation.

• If the fractional part contains more digits than the number of pattern the formatting string has, the number is rounded to the number of figures of fractional part equal to number of patterns.

• Fractional representation. The value is displayed in the form of a simple fraction consisting of a numerator and denominator, or as a mixed number containing integer part and a proper fraction.

• The formatting string for fractional representation of numbers consists of three substrings: for integer part, for numerator and denominator. The numerator and denominator are separated by the character of a slash “/”, and integer part is separated from the numerator by the closest space to the left of the slash.

• Integer part of fractional representation is formatted similarly to integer part of the decimal one.

• The numerator of the fraction is formatted similarly to integer number, however, it cannot be divided into groups and does not contain the sign +/–, that is why characters “+” and “&” are not considered as patterns.

• The number of digits in representation of fraction denominator is equal to the number of patterns of digits in the formatting substring for the denominator. If the number value cannot be expressed by a fraction with specified number of digits of the denominator precisely, the system finds a fraction which is the best approximation of this value.

• If displayed number is integer, the fractional part is not displayed.

• If the format string of integer part is absent (there is no space to the left of the character “/”), a fraction consisting of a numerator and a denominator is displayed. If the number value exceeds 1, improper fraction will be displayed, that is, the one in which the numerator is greater than the denominator.

• If both a slash and a decimal separator are present in the formatting string, the number is displayed in decimal format.

• Scientific (exponential) representation. In this representation the number is displayed as a product of the number in decimal representation (mantissa) to some power of number 10.

• The formatting string for scientific representation consists of a substring of mantissa's format separated from the substring of exponent format by a Latin letter “e” or “E”.

• The exponent is calculated so that the number of digits of integer part of the number was equal to the number of patterns in the corresponding substring.

• Formatting string of mantissa can be in decimal as well as in fractional representation.

• The exponent is formatted as integer number with the exception that it cannot be split into groups of digits separated by spaces.

• Percentage representation. The number value is displayed in percentage, that is, it is multiplied by 100 before it is displayed.

• The formatting string should have the character “%” for this representation. Part of the string to the left of “%” is used for formatting of a number the value of which is multiplied by 100. At the end of the number the sign of percentage and all characters specified in the formatting string to the right of this sign are printed.

• In this representation the part of the formatting string which is used for formatting the value can specify any representations of numbers described above (integer, decimal, fractional, exponent).

• Angular representation. The value of number is displayed in the form of a number of degrees and possibly of angular minutes and seconds.

• Displayed number of degrees is always in the range from 0 to 360. If the value of the formatting number goes beyond this range, remainder from division by 360 is taken. For example, number 500 is displayed as 140°, and number –10 – as 350°.

• In the formatting string the character “*” is always present for angular representation. In this case this character represents the symbol of a degree (since it is rather difficult to enter more usual character “°” from the keyboard). Such characters as an apostrophe “'” and quotes “"” can be also included into the string. They denote angular minutes (1/60 of a degree) and angular seconds (1/60 of a minute) accordingly. For formatting number of degrees, minutes and seconds numeric patterns 0, ? and # are used. For seconds decimal representation with indicated number of digits of the fractional part can be also used.

• A special “quadrant” displaying format of angle values is also provided. The presence of a special pattern “QR”, that is, “quadrant” in the formatting string apart from degree character “*” is an indication of this format. In the course of display this pattern is replaced by one of the four indications of quadrant of plane: NE, SE, SW, or NW and the number of degrees is recalculated as it is shown in table 48-1.

Table 48-1. Displaying angles in quadrant format

• It is also possible to use symbols of minutes and seconds in quadrant format.

There is a possibility to use different representations of positive and negative numbers. If the formatting string contains the character “;”, a part of the line located to the left of the semicolon is used for formatting positive and zero values, and the right part – for negative ones.

Formatting of numeric values by default (when the pattern “@” is specified) is determined on the basis of Windows settings.

Examples of formatting numeric values using different formatting strings are provided in table 48-2. It is assumed that a point is used as a decimal separator in Windows.

Table 48-2. Examples of formatting numeric values

Date format

For values of the type “Date” such elements as year, month, day of the week and day can be displayed in arbitrary order. The following patterns serve for highlighting date elements:

• yy – Year (two last digits);

• yyyy – Year (four digits);

• MMM – Month (short name: Jan, Feb, …);

• MMMM – Month (full name: January, February, …);

• M – Month (number of one or two digits: 1, 2, … , 9, 10, 11, 12);

• MM – Month (the number of two digits always: 01, 02, …);

• ddd – Day of week (short name: Su, Mo, Tu,…);

• dddd – Day of week (full name: Sunday, Monday, Tuesday, …);

• d – Day of month (of one or two digits: 1, 2, … , 30, 31);

• dd – Day of month (always of two digits: 01, 02, … , 30, 31).

All other characters of the formatting string are displayed without changes.

Formatting of date value by default is determined on the basis of Windows settings (when the pattern “@” is specified).

In table 48-3 examples of formatting dates are given.

Table 48-3. Examples of formatting date values

Time format

Values of the type “Time” consist of numbers of hours, minutes and seconds which can be displayed in any combination and in arbitrary order. To select time elements the following patterns are used:

• H – Hours (the number of one or two digits from 0 to 23);

• HH – Hours (the number always has two digits from 00 to 23);

• h – Hours (the number of one or two digits from 1 to 12);

• hh – Hours (the number always has two digits from 01 to 12);

• m – Minutes (the number of one or two digits from 0 to 59);

• mm – Minutes (the number always has two digits from 00 to 59);

• s – Seconds (the number of one or two digits from 0 to 59);

• ss – Seconds (the number always has two digits from 00 to 59);

• t – Indication “AM” (forenoon) or PM (afternoon).

All other characters of the formatting string are displayed without changes.

Formatting of time value by default is determined on the basis of Windows settings (when the pattern “@” is specified).

In table 48-4 examples of formatting time are given.

Table 48-4. Examples of formatting time values

Timestamp format

Since this type of data combines the date and time, the formatting string can have patterns belonging both to the date and to the time.

All the characters of the formatting string belonging to neither date patterns, not time patterns are displayed without changes.

Formatting of timestamp values is determined by default on the basis of Windows settings (when the pattern @ is specified).

In table 48-5 examples of formatting timestamp are given.

Table 48-5. Examples of formatting timestamp values

String format

Formatting of string values by default (the pattern “@”) denotes displaying a string as it is without any changes. Other patterns which can be used in the formatting string are the following ones:

• A – All letters are displayed as upper-case letters, the remaining characters are not changed;

• a – All letters are displayed as low-case letters, the remaining characters are not changed;

• Aa – First letters of each word are displayed as upper-case letters, the rest – as lower-case, the remaining characters are not changed.

If enumerated patterns are met several times in the formatting string, formatted string value will substitute each pattern.

In table 48-6 Examples of formatting string values are given.

Table 48-6. Examples of formatting string values

Boolean format

Words “yes” and “no” serve as default representation (the pattern “@”) for Boolean values.

The formatting string for Boolean values consists of two parts separated by the character of a slash “/”. Part of the string to the left of the separator is used as representation of true value, the right part – for representation of false value.

If there is no separator in the formatting string, the whole string is used as representation of true value, and an empty string will correspond to the false value.

In table 48-7 examples of formatting Boolean values are provided.

Table 48-7. Examples of formatting Boolean values

Mixed format

Specifying mixed format for a cell denotes that data of different types can be displayed in this cell. Displaying format is specified for every type of data individually. When values from the source of data are entered into cells, the format is selected in accordance with the type of these values.

When creating a table, the mixed format is set for all its cells.

Generally, the formatting string for the mixed format consists of six substrings separated by the sign of a vertical line “|”. These substrings correspond to different types of data in the following order: “Number”, “Date”, “Time”, “Timestamp”, “String”, “Boolean”. When data of some definite type is entered into a cell in the course of editing a table, the format specified for this type of data is selected for display. If one substring is missing for some definite format type (that is, if two vertical lines are typed one after another), the values of such a type will not be displayed.

If the formatting string contains less than 6 substrings, the result of display for the remaining types (for the ones for which no formatting substring is specified) will depend on whether the separator “|” is present at the end of the formatting string or no. If there is a separator, it is considered that empty formatting strings are specified for the remaining types, that is, the values of these types should not be displayed. If there is no separator at the end of the string, the remaining types are formatted by default as if the pattern “@” was specified for them.

If there are no separators in the formatting string, but the character “@” is present, then this string is used for formatting data of all types.