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.