VB format() function
Using the Visual Basic format() function
The Format function converts a value to a text string and gives you
control over the string's appearance. For example, you can specify the
number of decimal places for a numeric value, leading or trailing zeros,
currency formats, and portions of the date. The syntax is:
Syntax
Format(expression [,format [,firstdayofweek
[,firstweekofyear]]])
expression
Any valid expression
format
A valid named or user-defined format expression
firstdayofweek
A constant that specifies the first day of the week
firstweekofyear
A constant that specifies the first week of the year
The firstdayofweek argument has these settings:
| Constant |
Value |
Description |
| vbUseSystem |
0 |
Use NLS API setting. |
| vbSunday |
1 |
Sunday (default) |
| vbMonday |
2 |
Monday |
| vbTuesday |
3 |
Tuesday |
| vbWednesday |
4 |
Wednesday |
| vbThursday |
5 |
Thursday |
| vbFriday |
6 |
Friday |
| vbSaturday |
7 |
Saturday |
The firstweekofyear argument has these settings:
| Constant |
Value |
Description |
| vbUseSystem |
0 |
Use NLS API setting. |
| vbFirstJan1 |
1 |
Start with week in which January 1 occurs (default). |
| vbFirstFourDays |
2 |
Start with the first week that has at least four days in the
year. |
| vbFirstFullWeek |
3 |
Start with the first full week of the year. |
Notes
If you try to format a number without specifying format, Format
provides functionality similar to the Str function, although it is
internationally aware. However, positive numbers formatted as strings
using Format don’t include a leading space reserved for the sign of the
value; those converted using Str retain the leading space.
If you are formatting a non-localized numeric string, you should use
a user-defined numeric format to ensure that you get the look you want.
If the Calendar property setting is Gregorian and format specifies
date formatting, the supplied expression must be Gregorian. If the
Visual Basic Calendar property setting is Hijri, the supplied expression
must be Hijri.
If the calendar is Gregorian, the meaning of format expression
symbols is unchanged. If the calendar is Hijri, all date format symbols
(for example, dddd, mmmm, yyyy) have the same meaning but apply to the
Hijri calendar. Format symbols remain in English; symbols that result in
text display (for example, AM and PM) display the string (English or
Arabic) associated with that symbol. The range of certain symbols
changes when the calendar is Hijri.
Formatting Symbols
| Character |
Description |
None
No formatting |
Display the number with no formatting. |
| : |
Time separator. In some locales, other characters may be
used to represent the time separator. The time separator
separates hours, minutes, and seconds when time values are
formatted. The actual character used as the time separator in
formatted output is determined by your system settings. |
| / |
Date separator. In some locales, other characters may be
used to represent the date separator. The date separator
separates the day, month, and year when date values are
formatted. The actual character used as the date separator in
formatted output is determined by your system settings. |
| C |
Display the date as ddddd and display the time as t t t t t,
in that order. Display only date information if there is no
fractional part to the date serial number; display only time
information if there is no integer portion. |
| 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). |
| dddd |
Display the day as a full name (Sunday - Saturday). |
| ddddd |
Display the date as a complete date (including day, month,
and year), formatted according to your system's short date
format setting. The default short date format is m/d/yy. |
| dddddd |
Display a date serial number as a complete date (including
day, month, and year) formatted according to the long date
setting recognized by your system. The default long date format
is mmmm dd, yyyy. |
| w |
Display the day of the week as a number (1 for Sunday
through 7 for Saturday). |
| 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 m 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 year as a 2-digit number (00 - 99). |
| yyyy |
Display the year as a 4-digit number (100 - 9666). |
| 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). |
| t t t t t |
Display a time as a complete time (including hour, minute,
and second), formatted using the time separator defined by the
time format recognized by your system. 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 with any
hour before noon; display an uppercase PM with any hour between
noon and 11:59 P.M. |
| am/pm |
Use the 12-hour clock and display a lowercase AM with any
hour before noon; display a lowercase PM with any hour between
noon and 11:59 P.M. |
| A/P |
Use the 12-hour clock and display an uppercase A with any
hour before noon; display an uppercase P with any hour between
noon and 11:59 P.M. |
| a/p |
Use the 12-hour clock and display a lowercase A with any
hour before noon; display a lowercase P with any hour between
noon and 11:59 P.M. |
| AMPM |
Use the 12-hour clock and display the AM string literal as
defined by your system with any hour before noon; display the PM
string literal as defined by your system with any hour between
noon and 11:59 P.M. AMPM can be either uppercase or lowercase,
but the case of the string displayed matches the string as
defined by your system settings. The default format is AM/PM. |
0
Digit placeholder |
Display a digit or a zero. If the expression has a digit in
the position where the 0 appears in the format string, display
it; otherwise, display a zero in that position. If the number
has fewer digits than there are zeros (on either side of the
decimal) in the format expression, display leading or trailing
zeros. If the number has more digits to the right of the decimal
separator than there are zeros to the right of the decimal
separator in the format expression, round the number to as many
decimal places as there are zeros. If the number has more digits
to the left of the decimal separator than there are zeros to the
left of the decimal separator in the format expression, display
the extra digits without modification. |
#
Digit placeholder
|
Display a digit or nothing. If the expression has a digit in
the position where the # appears in the format string, display
it; otherwise, display nothing in that position. This symbol
works like the 0 digit placeholder, except that leading and
trailing zeros aren't displayed if the number has the same or
fewer digits than there are # characters on either side of the
decimal separator in the format expression. |
.
Decimal placeholder
|
In some locales, a comma is used as the decimal separator.
The decimal placeholder determines how many digits are displayed
to the left and right of the decimal separator. If the format
expression contains only number signs to the left of this
symbol, numbers smaller than 1 begin with a decimal separator.
If you always want a leading zero displayed with fractional
numbers, use 0 as the first digit placeholder to the left of the
decimal separator instead. The actual character used as a
decimal placeholder in the formatted output depends on the
Number Format recognized by your system. |
%
Percent placeholder |
The expression is multiplied by 100. The percent character
(%) is inserted in the position where it appears in the format
string. |
,
Thousand separator |
In some locales, a period is used as a thousand separator.
The thousand separator separates thousands from hundreds within
a number that has four or more places to the left of the decimal
separator. Standard use of the thousand separator is specified
if the format contains a thousand separator surrounded by digit
placeholders (0 or #). Two adjacent thousand separators or a
thousand separator immediately to the left of the decimal
separator (whether or not a decimal is specified) means "scale
the number by dividing it by 1000, rounding as needed." You can
scale large numbers using this technique. For example, you can
use the format string "##0,," to represent 100 million as 100.
Numbers smaller than 1 million are displayed as 0. Two adjacent
thousand separators in any position other than immediately to
the left of the decimal separator are treated simply as
specifying the use of a thousand separator. The actual character
used as the thousand separator in the formatted output depends
on the Number Format recognized by your system. |
:
Time separator |
In some locales, other characters may be used to represent
the time separator. The time separator separates hours, minutes,
and seconds when time values are formatted. The actual character
used as the time separator in formatted output is determined by
your system settings. |
/
Date separator |
In some locales, other characters may be used to represent
the date separator. The date separator separates the day, month,
and year when date values are formatted. The actual character
used as the date separator in formatted output is determined by
your system settings. |
E- E+ e- e+
Scientific format |
If the format expression contains at least one digit
placeholder (0 or #) to the right of E-, E+, e-, or e+, the
number is displayed in scientific format and E or e is inserted
between the number and its exponent. The number of digit
placeholders to the right determines the number of digits in the
exponent. Use E- or e- to place a minus sign next to negative
exponents. Use E+ or e+ to place a minus sign next to negative
exponents and a plus sign next to positive exponents. |
- + $ ( ) space
Display a literal character |
To display a character other than one of those listed,
precede it with a backslash (\) or enclose it in double
quotation marks (" "). |
\
Display the next character in the format string |
Many characters in the format expression have a special
meaning and can't be displayed as literal characters unless they
are preceded by a backslash. The backslash itself isn't
displayed. Using a backslash is the same as enclosing the next
character in double quotation marks. To display a backslash, use
two backslashes (\\). Examples of characters that can't be
displayed as literal characters are the date- and
time-formatting characters (a, c, d, h, m, n, p, q, s, t, w, y,
and /:), the numeric-formatting characters (#, 0, %, E, e,
comma, and period), and the string-formatting characters (@, &,
<, >, and !). |
"ABC"
Display the string inside the double quotation marks |
To include a string in format from within code, you must use
Chr(34) to enclose the text (34 is the character code for a
double quotation mark). |
@
Character placeholder |
Display a character or a space. If the string has a
character in the position where the @ appears in the format
string, display it; otherwise, display a space in that position.
Placeholders are filled from right to left unless there is an !
character in the format string. See below. |
&
Character placeholder |
Display a character or nothing. If the string has a
character in the position where the & appears, display it;
otherwise, display nothing. Placeholders are filled from right
to left unless there is an ! character in the format string. See
below. |
<
Force lowercase |
Display all characters in lowercase format. |
>
Force uppercase |
Display all characters in uppercase format. |
!
Force left to right fill of placeholders |
The default is to fill from right to left. |
Named Formats
Visual Basic provides several standard formats to use with the Format
function. Instead of using symbols, you specify these formats by name in
the format argument of the Format function. Always enclose the format
name in double quotation marks (""). The following table lists the
format names you can use.
| Named format |
Description |
| General Number |
Shows numbers as entered. |
| Currency |
Shows negative numbers inside parentheses. |
| Fixed |
Shows at least one digit. |
| Standard |
Uses a thousands separator. |
| Percent |
Multiplies the value by 100 with a percent sign at the end. |
| Scientific |
Uses standard scientific notation. |
| General Date |
Shows date and time if expression contains both. If
expression is only a date or a time, the missing information is
not displayed. |
| Long Date |
Uses the Long Date format specified in the Regional Settings
dialog box of the Microsoft Windows Control Panel. |
| Medium Date |
Uses the dd-mmm-yy format (for example, 03-Apr-93) |
| Short Date |
Uses the Short Date format specified in the Regional
Settings dialog box of the Windows Control Panel. |
| Long Time |
Shows the hour, minute, second, and "AM" or "PM" using the
h:mm:ss format. |
| Medium Time |
Shows the hour, minute, and "AM" or "PM" using the "hh:mm
AM/PM" format. |
| Short Time |
Shows the hour and minute using the hh:mm format. |
| Yes/No |
Any nonzero numeric value (usually - 1) is Yes. Zero is No. |
| True/False |
Any nonzero numeric value (usually - 1) is True. Zero is
False. |
| On/Off |
Any nonzero numeric value (usually - 1) is On. Zero is Off. |
Multiple Formats
A user-defined format expression can have from one to four sections
separated by semicolons. (If the format argument contains one of the
named formats, only one section is allowed.)
| If you use |
The result is |
| One section |
The format expression applies to all values. |
| Two sections |
The first section applies to positive values and zeros, the
second to negative values. |
| Three sections |
The first section applies to positive values, the second to
negative values, and the third to zeros. |
| Four sections |
The first section applies to positive values, the second to
negative values, the third to zeros, and the fourth to Null
values. |
The following example has two sections: the first defines the format
for positive values and zeros; the second section defines the format for
negative values.
$#,##0;($#,##0)
If you include semicolons with nothing between them, the missing section
is printed using the format of the positive value. For example, the
following format displays positive and negative values using the format
in the first section and displays "Zero" if the value is zero.
$#,##0;;\Z\e\r\o
Note If you try to format a number without specifying format,
Format provides the same functionality as the Str function. However,
positive numbers formatted as strings using Format lack the leading
space reserved for displaying the sign of the value; whereas, those
converted using Str retain the leading space.
Examples
The following conversions assume that the country in the Windows
Control Panel is set to "English (United States)."
| Format syntax |
Result |
| Format(8315.4, "00000.00") |
08315.40 |
| Format(8315.4, "#####.##") |
8315.4 |
| Format(8315.4, "##,##0.00") |
8,315.40 |
| Format(315.4, "$##0.00") |
$315.40 |
| Format(7, "0.00%") |
700.00% |
| Format("This Is A Test", "<") |
this is a test |
| Format("This Is A Test", ">") |
THIS IS A TEST |
| Format(Now, "m/d/yy") |
1/27/93 |
| Format(Now, "dddd, mmmm dd, yyyy") |
Wednesday, January 27, 1993 |
| Format(Now, "d-mmm") |
27-Jan |
| Format(Now, "mmmm-yy") |
January-93 |
| Format(Now, "hh:mm AM/PM") |
07:18 AM |
| Format(Now, "h:mm:ss a/p") |
7:18:00 a |
| Format(Now, "d-mmmm h:mm" |
27-January 7:18 |
| Format(Now, "d-mmmm-yy") |
27-January-93 |
| Format(Now, "d mmmm") |
27 January |
| Format(Now, "mmmm yy") |
January 93 |
| Format(Now, "hh:mm AM/PM") |
08:50 PM |
| Format(Now, "h:mm:ss a/p") |
8:50:35 p |
| Format(Now, "h:mm") |
20:50 |
| Format(Now, "h:mm:ss") |
20:50:35 |
| Format(Now, "m/d/yy h:mm") |
1/27/93 20:50 |
| Format (format) |
Positive 5 |
Negative 5 |
Decimal .5 |
Null |
| Zero-length string |
5 |
-5 |
0.5 |
|
| 0 |
5 |
-5 |
1 |
|
| 0.00 |
5.00 |
-5.00 |
0.50 |
|
| #,##0 |
5 |
-5 |
1 |
|
| #,##0.00;;;Nil |
5.00 |
-5.00 |
0.50 |
Nil |
| $#,##0;($#,##0) |
$5 |
($5) |
$1 |
|
| $#,##0.00;($#,##0.00) |
$5.00 |
($5.00) |
$0.50 |
|
| 0% |
500% |
-500% |
50% |
|
| 0.00% |
500.00% |
-500.00% |
50.00% |
|
| 0.00E+00 |
5.00E+00 |
-5.00E+00 |
5.00E-01 |
|
| 0.00E-00 |
5.00E00 |
-5.00E00 |
5.00E-01 |
|
Credit
Most of this is a re-hash of the documentation available from
Microsoft. But Microsoft has its Format() documentation spread out in a
number of loosely related entries, instead of being in one place.
Also check out the
VB Format
demo at 4 Guys from Rolla.
|