Some sample format expressions for numbers are shown below. (These examples all assume the Country is set to United States in the International section of the Control Panel.) The first column contains the format strings. The other columns contain the output the results if the formatted data has the value given in the column headings
Format (fmt) |
Positive 3 |
Negative 3 |
Decimal .3 |
Null |
Null string |
3 |
-3 |
0.3 |
|
0 |
3 |
-3 |
1 |
|
0.00 |
3.00 |
-3.00 |
0.30 |
|
#,##0 |
3 |
-3 |
1 |
|
#,##0.00;;;Nil |
3.00 |
-3.00 |
0.30 |
Nil |
$#,##0;($#,##0) |
$3 |
($3) |
$1 |
|
$#,##0.00;($#,##0.00)$3.00 |
($3.00) |
$0.30 |
|
|
0% |
300% |
-300% |
30% |
|
0.00% |
300.00% |
-300.00% |
30.00% |
|
0.00E+00 |
3.00E+00 |
-3.00E+00 |
3.00E-01 |
|
0.00E-00 |
3.00E00 |
-3.00E00 |
3.00E-01 |
|
Numbers can also be used to represent date and time information. You can format date and time serial numbers using date and time formats or number formats because date/time serial numbers are stored as floating-point values.
To format dates and times, you can use either the commonly used format that have been predefined or create user-defined time formats using standard meaning of each:
The following table shows the predefined data format names you can use and the meaning of each.
Format
Name |
Description |
General |
Display a date and/or time. for real numbers, display a date and time.(e.g. 4/3/93 03:34 PM); If there is no fractional part, display only a date (e.g. 4/3/93); if there is no integer part, display time only (e.g. 03:34 PM). |
Long Date |
Display a Long Date, as defined in the International section of the Control Panel. |
Medium |
Display a date in the same form as the Short Date, as defined in the international section of the Control Panel, except spell out the month abbreviation. |
Short Date |
Display a Short Date, as defined in the International section of the Control Panel. |
Long Time |
Display a Long Time, as defined in the International section of the Control panel. Long Time includes hours, minutes, seconds. |
Medium Time |
Display time in 12-hour format using hours and minutes and the Time AM/PM designator. |
Short Time |
Display a time using the 24-hour format (e.g. 17:45) |
This table shows the characters you can use to create user-defined date/time formats.
Character |
Meaning |
c |
Display the date as dddd and display the time as ttttt. in the order. |
d |
Display the day as a number without a leading zero (1-31). |
dd |
Display the day as a number with a leading zero (01-31). |
ddd |
Display the day as an abbreviation (Sun-Sat). |
ddddd |
Display a date serial number as a complete date (including day , month, and year). |
Character |
Meaning |
w |
Display the day of the week as a number (1- 7 ). |
ww |
Display the week of the year as a number (1-53). |
m |
Display the month as a number without a leading zero (1-12). If m immediately follows h or hh, the minute rather than the month is displayed. |
mm |
Display the month as a number with a leading zero (01-12). If mm immediately follows h or hh, the minute rather than the month is displayed. |
mmm |
Display the month as an abbreviation (Jan-Dec). |
mmmm |
Display the month as a full month name (January-December). |
q |
Display the quarter of the year as a number (1-4). |
y |
Display the day of the year as a number (1-366). |
yy |
Display the day of the year as a two-digit number (00-99). |
yyyy |
Display the day of the year as a four-digit number (100-9999). |
h |
Display the hour as a number without leading zeros (0-23). |
hh |
Display the hour as a number with leading zeros (00-23). |
n |
Display the minute as a number without leading zeros (0-59). |
nn |
Display the minute as a number with leading zeros (00-59). |
s |
Display the second as a number without leading zeros (0-59). |
ss |
Display the second as a number with leading zeros (00-59). |
tttt |
Display a time serial number as a complete time (including hour, minute, and second) formatted using the time separator defined by the Time Format in the International section of the Control Panel. A leading zero is displayed if the Leading Zero option is selected and the time is before 10:00 A.M. or P.M. The default time format is h:mm:ss. |
AM/PM |
Use the 12-hour clock and display an uppercase AM/PM |
am/pm |
Use the 12-hour clock display a lowercase am/pm |
The Following are examples of user-defined date and time formats:
Format |
Display |
m/d/yy |
2/26/65 |
d-mmmm-yy |
26-February-65 |
d-mmmm |
26 February |
mmmm-yy |
February 65 |
hh:nn AM/PM |
06:45 PM |
h:nn:ss a/p |
6:45:15 p |
h:nn:ss |
18:45:15 |
m/d/yy/h:nn |
2/26/65 18:45 |
Strings can also be formatted with Format[$]. A format expression for strings can have one section or two sections separated by a semicolon.
If you use |
The result is |
One section only |
The format applies to all string data. |
Two sections |
The first section applies to string data, the second to Null values and zero-length strings. |
The following characters can be used to create a format expression for strings:
Character |
Meaning |
@ |
Character placeholder. Displays a character or a space. Placeholders are filled from right to left unless there is an ! character in the format string. |
& |
Character placeholder. Display a character or nothing. |
< |
Force lowercase. |
> |
Force uppercase. |
! |
Force placeholders to fill from left to right instead of right to left. |
Related Topics: Str, Str$ Function.
More: