Number formats
Some of the content in this topic may not be applicable to some languages.
You can change the formats of numbers in a spreadsheet by using the Commands and Options dialog box, both in the design program and at run time in the browser. The settings in Microsoft Windows Control Panel also determine how number, currency, date, and time formats are displayed. For example, these settings determine whether years are displayed as two digits or four digits. See Windows Help for information about these formats.
In Microsoft Visual Basic or a script, you can assign custom number formats to the data. You can also type custom formats available in the spreadsheet object model in the Number format box. To find out more, see how to get help for programming a spreadsheet.
The following number formats are available in the Number format list on the Format tab of the Commands and Options dialog box.
| Format | Effects |
|---|---|
| General date | Displays a number as a date and optional time. For example, 8/31/99 05:54 AM.
The fractional part of the number is the time. If a number has no fractional part, only a date appears. If a number has only a fractional part and no integer part, only a time appears. |
| Long date | Displays a date according to the Windows regional setting for the long date format. For example, Thursday, January 25, 2001. |
| Medium date | Displays a date according to the Windows regional setting for the month and date separator, and displays two digits for the year. For example, Jan/25/01. |
| Short date | Displays a date according to the Windows regional setting for the short date format. For example, 1/25/01. |
| Long time | Displays a time according to the Windows regional setting for the long time format. For example, 8:45:36 PM. |
| Medium time | Displays a time in 12-hour format with an AM or PM designator, omitting the seconds. For example, 8:45 PM. |
| Short time | Displays a time in 24-hour format, omitting the seconds. For example, 20:45. |
| General | Displays a number right-aligned, with no thousands separator, and preceded by a minus sign if negative. Displays up to 11 digits, or 10 digits with a decimal point, rounding additional digits to the right of the decimal point. Displays numbers with more than 11 digits to the left of the decimal point in scientific format. Displays text left-aligned. |
| Currency | Displays a number according to the Windows regional setting for currency, with a thousands separator if appropriate and two decimal places. For example, with the default English (United States) regional settings, $2,532.75. |
| Euro currency | Displays a number as euro currency, with a thousands separator and two decimal places. The euro sign is placed before or after the number according to the Windows regional setting for currency. For example, with the default English (United States) regional setting,  12.35. |
| Fixed | Displays a number with two decimal places. For example, 68.30. |
| Standard | Displays a number with a thousands separator and two digits to the right of the decimal point. The Windows regional setting for numbers determines what's used for the thousands separator. For example, with a comma as the thousands separator, 1,800.00. |
| Percent | Displays a number multiplied by 100, with two decimal places and a percent sign. For example, the value 0.8914 would be displayed as 89.14%. |
| Scientific | Displays a number in exponential notation. For example, 1.25E+10. |
| Yes/no | Displays No if the number is 0; displays Yes for any other number. |
| True/false | Displays False if the number is 0; displays True for any other number. |
| On/off | Displays Off if the number is 0; displays On for any other number. |