|
<< Click to Display Table of Contents >> Data Format |
  
|
|
<< Click to Display Table of Contents >> Data Format |
|
As shown in the above figure, the available values for the format are: None, Date, Number, Currency, Percent, Text. The following describes the case of selecting different values.
[None] When no format is set, the default is empty.
[Date] Format data for date, timestamp, and time type. When the selected value is a date, a date format drop-down list will be displayed below the format drop-down list. This date format supports users to select the date provided by the product. The format also supports user-defined format types.
1. The format is as follows
Format |
Raw data |
Formatted data |
|---|---|---|
MM/dd/yyyy |
2009-01-01 |
01/01/2009 |
yyyy-MM-dd |
2009-01-01 |
2009-01-01 |
EEEEE, MMMMM dd, yyyy |
2009-01-01 |
Thu, January 01, 2009 |
MMMM d, yyyy |
2009-01-01 |
January 1, 2009 |
MM/d/yy |
2009-01-01 |
01/1/2009 |
d-MMM-yy |
2009-01-01 |
1-January-09 |
MM.d.yyyy |
2009-01-01 |
01.1.2009 |
MMM. d, yyyy |
2009-01-01 |
January. 1, 2009 |
d MMMMM yyyy |
2009-01-01 |
1 January 2009 |
MMMMM yy |
2009-01-01 |
January 09 |
MM-yy |
2009-01-01 |
01-09 |
MM/dd/yyyy hh:mm a |
2010-03-12 14:23:46 |
03/12/2010 02:23 PM |
MM/dd/yyyy HH:mm |
2010-03-12 14:23:46 |
03/12/2010 14:23 |
MM/dd/yyyy hh:mm:ss a |
2010-03-12 14:23:46 |
03/12/2010 02:23:46 PM |
h:mm a |
14:03:04 |
2:03 PM |
h:mm:ss a |
04:02:02 |
4:02:02 PM |
h:mm:ss a, z |
04:02:02 |
4:02:02 PM, CST |
GGGG yyyy/M/dd |
2009-01-01 |
AD 2009/1/01 |
GGGG yyyy/MM/dd |
2009-01-01 |
AD 2009/01/01 |
GGGG yyyy{Year}M{Month}dd{unit.day} |
2009-01-01 |
AD 2009Year1Month01Day |
GGGG yyyy{Year}MM{Month}dd{unit.day}(E) |
2009-01-01 |
AD 2009Year01Month01Day(Thu) |
2. Custom date format
The user can directly input the desired date format in the input box of the date format drop-down list. For example, enter "yy-MM-dd HH:mm", the original data is 2010-03-12 14:23:46, and the formatted data 10-03-12 14:23.
[Number] Set the format for numeric data. When the number is selected, a number format drop-down list will be displayed below. This number format drop-down list supports both the user's choice of own format and user-defined.
1. The format is as follows
Format |
Raw data |
Formatted data |
|---|---|---|
#,##0.00 |
10 |
10.00 |
|
10000.345 |
10,000.34 |
|
-34.466 |
-34.47 |
#,##0.## |
10 |
10 |
|
-34.466 |
-34.47 |
#,##0.00;(#,##0.00) |
10000.345 |
10,000.34 |
|
-34.466 |
(34.47) |
#,###千(中文) |
123456 |
123千 |
#,###K(英文THOUSAND) |
123456 |
123K |
#,###万(中文) |
126789 |
13万 |
#,###百万(中文) |
1234678 |
1百万 |
#,###M(英文MILLION) |
123000000 |
123M |
#,###亿(中文) |
123000000 |
1亿 |
#,###B(英文BILLION) |
12300000000 |
12B |
#,##0% |
-34.466 |
-3,447% |
##0.#####E0 |
0.230000034 |
230.00003E-3 |
￥#,##0.00;(￥#,##0.00)(人民币） |
10 |
￥10.00 |
|
-34.466 |
(￥34.47) |
$#,##0.00;($#,##0.00)(美元) |
-34.466 |
($34.47) |
2. Custom date format
The user can directly input the desired number format in the input box of the number format drop-down list.
For example,enter "#,##0.000", original data is 12.456789, formatted data is 12.457, input M, original data is 12300000000, formatted is M1230.
[Currency] Set the format for the digital type of data and display it in currency, RMB is ¥ and USD is $. Same as in the number format of ¥#,##0.00;(¥#,##0.00) and $#,##0.00;($#,##0.00), two decimal places are reserved by default.
[Percent] Sets the format for digital type data and presents it as a percentage. Same as #,##0% in number format.
[Text] Set the string format.When the text is selected, an input box is displayed below, and the user can set the format in this input box. You can write a string directly, or you can write {0} to refer to the value of the current data.
➢For Example:The format set in the first column of the table is a string: Direct input string; the second column sets the format to refer to the current value:{0}_ Reference raw data.The raw data and the formatted data are shown in the figure:
The user can also set the data of numeric type and date type.
1）The date,time and timestamp fields are formatted as follows:
date: {0,date,yyyy-MM-dd}
time: {0,date,HH:mm:ss}
timestamp: {0,date,yyyy-MM-dd HH:mm:ss}
The above yyyy-MM-dd, HH:mm:ss, yyyy-MM-dd HH:mm:ss can be of the date format type, users can set according to their needs.
➢For Example:Enter {0,date,yyyy/MM/dd HH:mm:ss} in the text. The original data and formatted data are shown in Figure:
2）Numeric type field format settings as follows:
currency: {0,number,currency}，
percent: {0,number,percent}
number: {0,number,#,##0.00}
The currency have 2 forms，RMB is￥, USD id $, If it is written as currency, the currency symbol is consistent with the current environment.
The digital format can be of the number format type, users can set according to their needs.
➢For Example: Set the data format of the fields a, b, and c, respectively, and enter in the text:{0,number,currency}, {0,number,#,##0.00}, {0,number,percent}, The original data and the formatted data are shown in the figure:
Also supports "\n" for line breaks in text, for example, enter {0,number, RMB\n} in the text, The original data and the format of the data display are shown in the figure:
