The Format() Function
The Format() function is a general-purpose formatting function that returns a string value formatted according to a format string. The format strings duplicate numeric and date/time formats produced by the specialized formats described above. The general format for applying the Format() function is shown below.
| Format(value, "format string") |
Formatting Numbers
A format string for numeric values can use one of the predefined string values shown in the following table.
| String |
Description |
| General Number|G|g |
Displays number with no thousand separator. |
| Currency|C|c |
Displays number with thousand separator, if appropriate; display two digits to the right of the decimal separator. |
| Fixed|F|f |
Displays at least one digit to the left and two digits to the right of the decimal separator. |
| Standard|N|n |
Displays number with thousand separator, at least one digit to the left and two digits to the right of the decimal separator. |
| Percent |
Displays number multiplied by 100 with a percent sign (%) appended immediately to the right; always displays two digits to the right of the decimal separator. |
| P|p |
Displays number with thousandths separator multiplied by 100 with a percent sign (%) appended to the right and separated by a single space; always displays two digits to the right of the decimal separator. |
Examples of applying a format string to numeric values are shown in the following table.
| Format |
Output |
| Format(12345.6789,"g") |
12345.6789 |
| Format(12345.6789,"c") |
$12,345.68 |
| Format(12345.6789,"f") |
12345.68 |
| Format(12345.6789,"n") |
12,345.68 |
| Format(-12345.6789,"g") |
-12345.6789 |
| Format(-12345.6789,"c") |
($12,345.68) |
| Format(-12345.6789,"f") |
-12345.68 |
| Format(-12345.6789,"n") |
-12,345.68 |
| Format(.6789,"Percent") |
67.89% |
| Format(.6789,"p") |
67.89 % |
| Format(-.6789,"Percent") |
-67.89% |
| Format(-.6789,"p") |
-67.89 % |
Formatting Dates and Times
A format string for date/time values can use one of the predefined string values shown in the following table.
| String |
Description |
| Long Date|D |
Displays a date in long date format. |
| Short Date|d |
Displays a date in short date format. |
| Long Time|T |
Displays a date in long date format. |
| Short Time|t |
Displays a date in short date format. |
| F |
Displays the long date and long time. |
| f |
Displays the long date and short time. |
| g |
Displays the short date and short time. |
| M|m |
Displays the month and the day of a date. |
| Y|y |
Formats the date as the year and month. |
Examples of applying a format string to date/time values are shown in the following table.
| Format |
Output |
| Format(Now,"D") |
Thursday, June 21, 2007 |
| Format(Now,"d") |
6/21/2007 |
| Format(Now,"T") |
9:10:57 PM |
| Format(Now,"t") |
9:10 PM |
| Format(Now,"F") |
Thursday, June 21, 2007 9:10:57 PM |
| Format(Now,"f") |
Thursday, June 21, 2007 9:10 PM |
| Format(Now,"g") |
6/21/2007 9:10 PM |
| Format(Now,"m") |
June 21 |
| Format(Now,"y") |
June, 2007 |
User-defined Numeric Formats
Formats can be defined for displaying numeric values by composing a string to describe the format. This user-defined string is applied through the Format() function. The characters shown in the following table are used to compose the format string.
| Character |
Description |
| 0 |
Digit placeholder. Displays a digit or a zero. If the value has a digit in the position, then it displays; otherwise, a zero is displayed. |
| # |
Digit placeholder. Displays a digit or a space. If the value has a digit in the position, then it displays; otherwise, a space is displayed. |
| . |
Decimal placeholder; determines how many digits are displayed to the left and right of the decimal separator. |
| , |
Thousand separator; separates thousands from hundreds within a number that has four or more places to the left of the decimal separator. Only a single "," is required in the format, between the first set of digit placeholders. |
| % |
Percent placeholder. Multiplies the expression by 100. The percent character (%) is inserted in the position where it appears in the format string. |
| - + $ ( ) |
Literal characters; displayed exactly as typed in the format string. |
Examples of applying a user-defined format string to numeric values are shown in the following table.
| Format |
Output |
| Format(012345.6789,"0.00") |
12345.68 |
| Format(012345.6789,"0,0.000") |
12,345.679 |
| Format(012345.6789,"00000,0.000000") |
012,345.678900 |
| Format(012345.6789,"#.##") |
12345.68 |
| Format(012345.6789,"#,#.##") |
12,345.68 |
| Format(012345.6789,"$ #,#.##") |
$ 12,345.68 |
| Format(-012345.6789,"#,#.####") |
-12,345.6789 |
| Format(-012345.6789,"$#,#.##") |
-$12,345.68 |
| Format(.6789,"#,#.##") |
.68 |
| Format(.6789,"0,0.000") |
00.679 |
| Format(-.6789," 0.0000") |
- 0.6789 |
| Format(.6789,"0.00%") |
67.89% |
User-defined Date/Time Formats
Formats can be defined for displaying date and time values by composing a string to describe the format. This user-defined string is applied through the Format() function. The characters shown in the following table are used to compose the date/time format string.
| Character |
Description |
| : |
Time separator. |
| / - |
Date separators. |
| % |
Precedes a single-character format string. |
| d |
Displays the day as a number without a leading zero. |
| dd |
Displays the day as a number with a leading zero. |
| ddd |
Displays the day name as an abbreviation. |
| dddd |
Displays the day as a full name. |
| M |
Displays the month as a number without a leading zero. |
| MM |
Displays the month as a number with a leading zero. |
| MMM |
Displays the month name as an abbreviation. |
| MMMM |
Displays the month as a full name. |
| yy |
Displays the year in two-digit format. |
| yyyy |
Displays the year in four-digit format. |
| h |
Displays the hour as a number without leading zeros using the 12-hour clock. |
| hh |
Displays the hour as a number with leading zeros using the 12-hour clock. |
| H |
Displays the hour as a number without leading zeros using the 24-hour clock. |
| HH |
Displays the hour as a number with leading zeros using the 24-hour clock. |
| m |
Displays the minute as a number without leading zeros. |
| mm |
Displays the minute as a number with leading zeros. |
| s |
Displays the seconds as a number without leading zeros. |
| ss |
Displays the seconds as a number with leading zeros. |
| f... |
Displays fractions of seconds using up to 7 characters to display fractional digits. |
| tt |
Uses the 12-hour clock and displays an uppercase AM with any hour before noon; displays an uppercase PM with any hour between noon and 11:59 P.M. |
| |
Additional characters and punctuation marks can be used within the format string. Characters that match any of the formatting characters must be preceded by "\". |
Examples of applying a user-defined format string to date/time values are shown in the following table.
| Format |
Output |
| Format(Now,"M/d/yy") |
6/21/07 |
| Format(Now,"M-d-yyyy") |
6-21-2007 |
| Format(Now,"d-MMMM-yy") |
21-June-07 |
| Format(Now,"d MMMM, yyyy") |
21 June, 2007 |
| Format(Now,"MMMM d, yyyy") |
June 21, 2007 |
| Format(Now,"MMMM, yyyy") |
June, 2007 |
| Format(Now,"%d") |
21 |
| Format(Now,"h:m tt") |
9:10 PM |
| Format(Now,"h:m:ss tt") |
9:10:57 PM |
| Format(Now,"H:m") |
21:10 |
| Format(Now,"M/d/yy - h:mtt") |
6/21/07 - 9:10PM |
| Format(Now,"H:m:ss.fffffff") |
21:10:57.5829544 |
| Format(Now,"To\da\y i\s MMMM d, yyyy.") |
Today is June 21, 2007. |