|
|
Formatting Codes
Use the Number property to set the display format of numbers and dates. Number may be a property of the Style object or of Cell.Format. The table below lists predefined formats. You can also define custom formats, for example, a timestamp format:
set TimeStampStyle = XLW.CreateStyle TimeStampStyle.Number = "mm/dd/yyyy hh:mm:ss AM/PM"
A custom format may include four format code sections, separated by semicolons (;). Excel reads the four sections as follows.
- Section 1 defines a format for positive numbers
- Section 2 defines a format for negative numbers
- Section 3 defines a format for zero values
- Section 4 defines a format for text
When you skip a section, include the empty section's ending semicolon. If you include less than four sections, in order, do not include a semicolon after the last section.
| Format.Number Value | Format String |
|---|---|
| 0 | General |
| 1 | 0 |
| 2 | 0.00 |
| 3 | #,##0 |
| 4 | #,##0.00 |
| 5 | ($#,##0_);($#,##0) |
| 6 | ($#,##0_);($#,##0) |
| 7 | ($#,##0.00_);($#,##0.00) |
| 8 | ($#,##0.00_);($#,##0.00) |
| 9 | 0% |
| 10 | 0.00% |
| 11 | 0.00E+00 |
| 12 | # ?/? |
| 13 | # ??/?? |
| 14 | m/d/yy |
| 15 | 0.00% |
| 16 | d-mmm |
| 17 | mmm-yy |
| 18 | h:mm AM/PM |
| 19 | h:mm:ss AM/PM |
| 20 | h:mm |
| 37 | (#,##0_);(#,##0) |
| 38 | (#,##0_);(#,##0) |
| 39 | (#,##0.00_);(#,##0.00) |
| 40 | (#,##0.00_);(#,##0.00) |
| 41 | _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_) |
| 42 | _($* #,##0_);_($* (#,##0);_(* "-"_);_(@_) |
| 43 | _(* #,##0.00_);_(* (#,##0.00);_(* "-"_);_(@_) |
| 44 | _($* #,##0.00_);_($* (#,##0.00);_(* "-"_);_(@_) |
| 45 | mm:ss |
| 46 | [h]:mm:ss |
| 47 | mm:ss.0 |
| 48 | ##0.0E+0 |
| 49 | @ (Format as text. The value will be displayed exactly as entered.) |
Copyright © 2005, SoftArtisans, Inc.