Number formats for PivotTable lists
Some of the content in this topic may not be applicable to some languages.
The regional settings in the Windows Control Panel determine how number, currency, date, and time formats are displayed. For example, these settings determine whether years are displayed with two digits or four digits. See Windows Help for information about these formats.
The following table lists the number formats supported in a PivotTable list:
|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.
|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 Microsoft Windows regional setting for the long date format. For example, Thursday, January 25, 2001.|
|Medium Date||Displays a date using the abbreviated month names and hyphens (-) to separate month, day, and year. For example, Jan-25-2001.|
|Short Date||Displays a date according to the Windows regional setting for the short date format. For example, 1/25/2001.|
|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.|
|Currency||Displays a number according to the Windows regional setting for currency. For example, with the default United States English regional settings, $2,532.75.|
|Euro Currency||Displays a number as euro currency, with a thousands separator and two decimal places. The placement of the euro sign and the decimal places are based on the regional settings for currency in Windows Control Panel. For example, with the default United States English regional settings, 12.35.|
|Fixed||Displays a number with two decimal places. For example, 68.30.|
|Standard||Displays a number according to the Windows regional settings for numbers. For example, with a comma as the thousands separator and a period as the decimal symbol, 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.|
Note In Microsoft Visual Basic or in a script, you can assign custom number formats to the data. You can also type custom formats available in the PivotTable list object model in the Number box.