Formats an expression according to instructions contained in a format expression.
Format(expression[, format[, firstdayofweek[, firstweekofyear]]])
The Format function syntax has these parts:
Part |
Description |
expression |
Any valid expression. |
format |
A valid named or user-defined format expression. |
firstdayofweek |
A constant that specifies the first day of the week, as described in Settings. |
firstweekofyear |
A constant that specifies the first week of the year, as described in Settings. |
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. |
To Format |
Do This |
Numbers |
Use predefined named numeric formats or create user-defined numeric formats. |
Dates and times |
Use predefined named date/time formats or create user-defined date/time formats. |
Date and time serial numbers |
Use date and time formats or numeric formats. |
Strings |
Create your own user-defined string formats. |
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.
Format Function — Formats for Different Numeric Values, Format Function — Formats for Different String Values, Format Function — Named Date/Time Formats, Format Function — Named Numeric Formats, Str Function, Format Function — User-Defined Date/Time Formats, Format Function — User-Defined Numeric Formats, Format Function — User-Defined String Formats.
This example shows various uses of the Format function to format values using both named and user-defined formats. For the date separator (/), time separator (:), and AM/ PM literal, the actual formatted output displayed by your system depends on the locale settings on which the code is running. When times and dates are displayed in the development environment, the short time and short date formats of the code locale are used. When displayed by running code, the short time and short date formats of the system locale are used, which may differ from the code locale. For this example, English/United States is assumed.
MyTime and MyDate are displayed in the development environment using current system short time and short date settings.
MyTime = #17:04:23#= #January 27, 1993# ' Returns current system time in the system-defined long time format.= Format(Time, "Long Time") ' Returns current system date in the system-defined long date format.= Format(Date, "Long Date") = Format(MyTime, "h:m:s") ' Returns "17:4:23".= Format(MyTime, "hh:mm:ss AMPM") ' Returns "05:04:23 PM".= Format(MyDate, "dddd, mmm d yyyy") ' Returns "Wednesday, ' Jan 27 1993". ' If format is not supplied, a string is returned.= Format(23) ' Returns "23". ' User-defined formats.= Format(5459.4, "##,##0.00") ' Returns "5,459.40".= Format(334.9, "###0.00") ' Returns "334.90".= Format(5, "0.00%") ' Returns "500.00%".= Format("HELLO", "<") ' Returns "hello".