To display a numeric value as a date, time, currency, or in a special format in VBA, use the Format() function, which returns a Variant (String) containing the expression formatted according to the format description.
Format(Expression[, Format[, FirstDayOfWeek [, FirstWeekOfYear]]])
- Expression — any valid expression.
- Format — any valid named or user-defined format expression. For example, the named format Fixed displays a numeric value with two decimal places. Named format examples are shown in Tables 1 and 2.
- FirstDayOfWeek — constant specifying the first day of the week.
- FirstWeekOfYear — constant specifying the first week of the year.
Table1. Named Numeric Formats
| Format Name | Description |
| General Number | Number without a thousands separator |
| Currency | Uses system regional settings. Displays two decimal digits |
| Fixed | At least one digit to the left and two to the right of the decimal point |
| Standard | At least one digit to the left, two to the right, and shows thousands separator |
| Percent | Displays the number as a percentage with two decimal digits |
| Scientific | Uses floating-point scientific notation |
| Yes/No | Displays No if the number is 0, Yes otherwise |
| True/False | Displays False if the number is 0, True otherwise |
| On/Off | Displays Off if the number is 0, On otherwise |
Table 2. Named Date and Time Formats
| Format Name | Description |
| General Date | Displays date or time. If no fractional part, displays date only |
| Long Date | Displays date according to Windows long date format |
| Medium Date | Displays date according to standard Windows date format |
| Short Date | Displays date according to short Windows date format |
| Long Time | Displays hours, minutes, and seconds |
| Medium Time | Displays hours and minutes in 12-hour format |
| Short Time | Displays hours and minutes in 24-hour format |
For example, the following code outputs the formatted values to the Immediate Window.
Examples of Named Formats
Sub Frm() Dim x As Double x = 4654646.544564 Debug.Print "General Number", Format(x, "General Number") Debug.Print "Currency", Format(x, "Currency") Debug.Print "Fixed", Format(x, "Fixed") Debug.Print "Standard", Format(x, "Standard") Debug.Print "Percent", Format(x, "Percent") Debug.Print "Scientific", Format(x, "Scientific") Debug.Print "Yes/No", Format(x, "Yes/No") Debug.Print "True/False", Format(x, "True/False") Debug.Print "On/Off", Format(x, "On/Off") Debug.Print "General Date", Format(Now, "General Date") Debug.Print "Long Date", Format(Now, "Long Date") Debug.Print "Medium Date", Format(Now, "Medium Date") Debug.Print "Short Date", Format(Now, "Short Date") Debug.Print "Long Time", Format(Now, "Long Time") Debug.Print "Medium Time", Format(Now, "Medium Time") Debug.Print "Short Time", Format(Now, "Short Time") End Sub
Formatted Values in the Immediate Window
