Supported Data Format

From Documentation
Revision as of 12:47, 19 January 2022 by Hawk (talk | contribs) (correct highlight (via JWB))
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)


Supported Data Format





Built-in Format

Category Format Pattern Input Result
Number
-1234.10 0.00 12345 12345.00
1234.10 0.00;[Red]0.00 -12345
12345.00
(1234.10) 0.00_);(0.00) 12345 12345.00
-12345 (12345.00)
(1234.10) 0.00_);[Red](0.00) 12345 12345.00
-12345
(12345.00)
Currency
-$1,234.10 $#,##0.00 12345 $12,345.00
$1,234.10 $#,##0.00;[Red]$#,##0.0 12345 $12,345.00
-12345
$12,345.0
($1,234.10) $#,##0.00_);($#,##0.00) 12345 $12,345.00
($12,345.00)
($1,234.10) $#,##0.00_);[Red]($#,##0.00) 12345 $12,345.00
-12345
($12,345.00)
Accounting
$1,234 _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_) 12345 $12,345.00
-12345 $ (12,345.00)
0 $-
Text Text
Date
*3/14/2001 yyyy/m/d 2013/11/8 2013/11/8
3/14/01 m/d/yy;@ 11/8/13
03/14/01 mm/dd/yy;@ 11/08/13
14-Mar-2001 d-mmm-yyyy;@ 8-Oct-2013
4-Mar-01 d-mmm-yy;@ 8-Oct-13
04-Mar-01 dd-mmm-yy;@ 08-Oct-13
3/14 m/d;@ 11/8
14-Mar d-mmm;@ 8-Oct
Mar-01 mmm-yy;@ Oct-13
March-01 mmmm-yy;@ October-13
3/14/01 13:30 m/d/yy h:mm;@ 11/8/13 10:55
3/14/01 1:30 PM m/d/yy h:mm AM/PM;@ 11/8/13 10:55 AM
Time
*1:30:55 PM AM/PM hh:mm:ss 10:55:30 AM 10:55:30
13:30 h:mm;@ 10:55
1:30 PM h:mm AM/PM;@ 10:55 AM
13:30:55 h:mm:ss;@ 10:55:30
1:30:55 PM h:mm:ss AM/PM;@ 10:55:30 AM
30:55.2 mm:ss.0;@ 55:30.0
37:30:55 [h]:mm:ss;@ 998074:55:30
*3/14/01 1:30 PM m/d/yy h:mm AM/PM;@ 11/8/13 10:55 AM
*3/14/01 13:30 m/d/yy h:mm;@ 11/8/13 10:55
Percentage
percentage 0.00% 0.95 95.00%
Fraction
Up to one digit # ?/? 0.25 1/4
Up to two digits # ??/?? 0.84 21/25
Up to three digits # ???/??? 0.33085896 312/943
As halves # ?/2 0.5 1/2
As quarters # ?/4 0.5 2/4
As eighths # ?/8 0.5 4/8
As sixteens # ??/16 0.3 8/16
As tenths # ?/10 0.3 3/10
As hundredths # ??/100 0.25 30/100
Scientific
scientific 0.00E+00 12345 1.23E+04
Text
text @ text text
Special
Zip Code 00000 12345 12345
Zip Code + 4 00000-0000 123451234 12345-1234
Phone Number [<=9999999]###-####;(###)###-#### 0123456 012-3456
Social Security Number 000-00-0000 123456789 123-45-6789


Syntax for Custom Number Format

To create a custom number format, you can start from one of the built-in number formats as a starting point. You can change it to create your own custom number format.

A number format can have up to 2 sections, separated by semicolons. These symbol sections define the format for positive numbers (including zero) and negative numbers respectively.

POSITIVE;NEGATIVE

For example, you can create the following custom format:

[Blue]#,##0.00_);[Red](#,##0.00)

You do not have to include all symbol sections in your custom number format. If you specify only one code section, it is used for all numbers.

For Including Text

To display both text and numbers in a cell, enclose the text characters in double quotation (" "). For example, type the format $0.00" Surplus";$-0.00" Shortage" to display a positive amount as "$125.74 Surplus" and a negative amount as "$-125.74 Shortage." Note that there is one space character before both "Surplus" and "Shortage" in each symbol section.


For using decimal places, spaces, colors, and conditions

Include decimal places and significant digits

To format fractions or numbers that contain decimal points, include the following digit placeholders, decimal points, and thousand separators in a section.

0 (zero) This digit placeholder displays insignificant zeros if a number has fewer digits than there are zeros in the format. For example, if you type 8.9, and you want it to be displayed as 8.90, use the format #.00.
# This digit placeholder follows the same rules as 0 (zero). However, Spreadsheet does not display extra zeros when the number that you type has fewer digits on either side of the decimal than there are # symbols in the format. For example, if the custom format is #.##, and you type 8.9 in the cell, the number 8.9 is displayed.
? This digit placeholder follows the same rules as 0 (zero). However, Spreadsheet adds a space for insignificant zeros on either side of the decimal point so that decimal points are aligned in the column. For example, the custom format 0.0? aligns the decimal points for the numbers 8.9 and 88.99 in a column.
. (period) This digit placeholder displays the decimal point in a number.

Display a thousands separator

To display a comma as a thousands separator or to scale a number by a multiple of 1,000, include the following separator in the number format.

, (comma) Displays the thousands separator in a number. Spreadsheet separates thousands by commas if the format contains a comma that is enclosed by number signs (#) or by zeros. A comma that follows a digit placeholder scales the number by 1,000. For example, if the format is #.0,, and you type 12,200,000 in the cell, the number 12.200.0 is displayed.


Specify colors

To specify the color of a section of the format, type the name of one of the following eight colors enclosed in square brackets in the section. The color code must be the first item in the section.

[Black]
[Green]
[White]
[Blue]
[Yellow]
[Red]


Specify Conditions

To specify number formats that will be applied only if a number meets a condition that you specify, enclose the condition in square brackets. The condition consists of a comparison operator and a value. For example, the following format displays numbers that are less than or equal to 100 in a red font and numbers that are greater than 100 in a blue font.

[Red][<=100];[Blue][>100]


For percentages, and scientific notation format

Display percentages

To display numbers as a percentage of 100 — for example, to display .08 as 8% or 2.8 as 280% — include the percent sign (%) in the number format.


Display scientific notations

To display numbers in scientific (exponential) format, use the following exponent codes in a section.


E Displays a number in scientific (exponential) format. For example, if the format is 0.00E+00, and you type 12,200,000 in the cell, the number 1.22E+07 is displayed. If you change the number format to #0.0E+0, the number 12.2E+6 is displayed.

Syntax for Custom Date and Time Formats

Display days, months, and years

To display numbers as date formats (such as days, months, and years), use the following symbols in a section.


m Displays month as a number without a leading zero
mm Displays month as a number with a leading zero when appropriate
mmm Displays month as an abbreviation (Jan to Dec).
mmmm Displays the month as a full name (January to December).
d Displays day as a number without a leading zero.
dd Displays day as a number with a leading zero when appropriate.
ddd Displays day as an abbreviation (Sun to Sat).
dddd Displays day as a full name (Sunday to Saturday).
yy Displays year as a two-digit number.
yyyy Displays year as a four-digit number.


Display hours, minutes, and seconds

To display time formats (such as hours, minutes, and seconds), use the following symbol in a section.

h Displays hour as a number without a leading zero.
[h] Displays elapsed time in hours. If you write a formula that returns a time in which the number of hours exceeds 24, use a number format like [h]:mm:ss.
hh Displays hour as a number with a leading zero when appropriate. If the format contains AM or PM, the hour is based on the 12-hour clock. Otherwise, the hour is based on the 24-hour clock.
m Displays minute as a number without a leading zero. The m or mm must appear immediately after the h or hh or immediately before the ss; otherwise, Spreadsheet displays it as month instead of minutes.
[m] Displays elapsed time in minutes. If you write a formula that returns a the number of minutes exceeds 60, use a format like [mm]:ss.
mm Displays minute as a number with a leading zero when appropriate. The m or mm must appear immediately after the h or hh or immediately before the ss ; otherwise, Spreadsheet displays it as month instead of minutes.
s Displays second as a number without a leading zero.
[s] Displays elapsed time in seconds. If you write a formula that returns the number of seconds which exceeds 60, use a number format that resembles [ss].
ss Displays second as a number with a leading zero when appropriate. If you want to display fractions of a second, use a format like h:mm:ss.00.
AM/PM, am/pm, A/P, a/p Displays hour using a 12-hour clock. Spreadsheet displays AM, am, or A, for times from midnight until noon and PM, pm or P for times from noon until midnight.



All source code listed in this book is at Github.


Last Update : 2022/01/19

Copyright © Potix Corporation. This article is licensed under GNU Free Documentation License.