Using the Format Function Formatting data can consume a lot of your time. Fortunately, the Format function provides a number of predefined formatting options. In addition, you can create user-defined formats for custom formatting needs. The possibilities are too numerous to adequately cover, but this section lists the tools you have at your disposal. CAUTION The most important thing to remember about the Format function is that the resulting value is a Variant String subtype the resulting value's data type does not remain the same. That means that although the Format function can make your data look good, you won't want to use it with values used in other calculations. When a value needs manipulating before calculating, look to the conversion functions for help.
The Format function uses the form
Format(expression[, format[, firstdayofweek[, firstweekofyear]]])
where expression is a String or Numeric data type that represents the value you're formatting. The optional arguments determine how expression is formatted. The format argument is one of many predefined or custom format expressions. You saw the last two, firstdayofweek and firstweekofyear, in the earlier section, "Extracting Date Components." Numbers, including date and time values and serial numbers, as well as strings, can all be formatted using the Format function. You use a unique set of named and user-defined format expressions, defined in Tables 5.5 and 5.6. Table 5.5. Numeric Named FormatsFormat | Example | Result |
|---|
General Number | Format(1234.5678, "General Number") | 1234.5678 | Currency | Format(1234.5678, "Currency") | $1,234.57 | Fixed | Format(0.1, "Fixed") | 0.10 | Standard | Format(1234.5678, "Standard") | 1,234.57 | Percent | Format(.5678, "Percent") | 56.78% | Scientific | Format(1234.5678, "Scientific") | 1.23E+03 | Yes/No | Format(0, "Yes/No") Format(2, "Yes/No") | No Yes | True/False | Format(0, "True/False") Format(2, "True/False") | False True | On/Off | Format(0, "On/Off") Format(2, "On/Off") | Off On |
Table 5.6. Date/Time Named FormatsFormat | Example | Result |
|---|
General Date | Format("04/01/04", "General Date") | 4/1/2004 | Long Date | Format("04/01/04", "Long Date") | Thursday, April 01, 2004 | Medium Date | Format("04/01/04", "Medium Date") | 01-Apr-04 | Short Date | Format("04/01/04", "Short Date") | 4/1/2004 | Long Time | Format("13:41:02", "Long Time") | 1:41:02 PM | Medium Time | Format("13:41:02", "Medium Time") | 1:41 PM | Short Time | Format("13:41:02", "Short Time") | 13:41 |
Most of the format expressions are self-explanatory, but a few deserve a bit more information. Currency relies on your system's locale settings for the appropriate symbols and thousand separators. Fixed always displays at least one digit to the left and two digits to the right of the decimal point. Standard is similar to Fixed except it also includes the thousands separator, as determined by the system's locale settings. Percent simply multiples the value by 100 and adds the percent symbol. Applying User-Defined Formats The named formats listed in Table 5.5 and Table 5.6 won't always be adequate. When they don't suffice, you can use the user-defined format characters to create your own. These formats are listed in Tables 5.7, 5.8, and 5.9. Table 5.7. Numeric User-Defined FormatsFormat | Explanation | Example | Result |
|---|
0 | Existing digit or 0 is displayed for each 0 | Format(12.3456, "000.00000") | 012.34560 | Rounds if more digits than placeholder characters | Format(12.3456, "000.00") | 012.35 | # | Displays existing digit or nothing | Format(12.3456, "###.#####") | 12.3456 | Rounds if more digits than placeholder characters | Format(12.3456, "###.##") | 12.35 | % | Multiples value by 100 and adds the percentage symbol | Format(.3456, "##%") | 35% | E- E+ e- e+ | Converts to scientific notation | Format(1.234567, "###E-###") | 123E-2 | | | Format(1.234567, "###e-###") | 123e-2 | - + $ () | Displays a literal character | Format(123.45, "$#####.##") | $123.45 | \ | Displays following character as a literal character | Format(.3456, "##.##\%") | .35% |
Table 5.8. Date User-Defined FormatsFormat | Explanation | Example | Result |
|---|
d | Displays day of month without a leading zero | Format("04/01/04", "d") | 1 | dd | Displays day of month with a leading zero when month is one character value | Format("04/01/04", "dd") | 01 | ddd | Displays day of week by abbreviated name | Format("04/01/04", "ddd") | Thu | dddd | Displays day of week by full name | Format("04/01/04", "dddd") | Thursday | ddddd | Displays short date | Format("04/01/04", "ddddd") | 4/1/2004 | dddddd | Displays long date | Format("04/01/04", "dddddd") | Thursday, April 01, 2004 | m | Displays month value without a leading zero | Format("04/01/04", "m") | 4 | mm | Displays month value with leading zero when month is single digit value | Format("04/01/04", "mm") | 04 | mmm | Displays month as abbreviated name | Format("04/01/04", "mmm") | Apr | mmmm | Displays month as full name | Format("04/01/04", "mmmm") | April | q | Displays yearly quarter | Format("04/01/04", "q") | 2 | h | Displays hour of the day without leading zeros | Format("9:41:02", "h") | 9 | hh | Displays hour of the day with leading zeros when hour is single digit value | Format("9:41:02", "hh") | 09 | n | Displays minute without leading zeros | Format("9:03:02", "n") | 3 | nn | Displays minute with leading zeros when minute is single digit value | Format("9:03:02", "nn") | 03 | s | Displays second without leading zeros | Format("9:03:02", "s") | 2 | ss | Displays second with leading zero when second is single digit value | Format("9:03:02", "ss") | 02 | ttttt | Displays 12-hour clock using locale settings | Format("13:41:02", "ttttt") | 1:41:02 PM | AM/PM | Displays 12-hour clock using uppercase AM/PM | Format("13:41:02","hh:mm AM/PM") | 01:41 PM | am/pm | Displays 12-hour clock using lowercase am/pm | Format("13:41:02", "hh:mm am/pm") | 01:41 pm | A/P | Displays 12-hour clock with uppercase A or P | Format("13:41:02", "hh:mm A/P") | 01:41 P | a/p | Displays 12-hour clock with lowercase a or p | Format("13:41:02", "hh:mm a/p") | 01:41 p | ww | Displays the week value, 1 to 54 | Format("04/01/04", "ww") | 14 | w | Displays the weekday value, 1 to 7 | Format("04/01/04", "w") | 5 | y | Displays a day value from 1 to 366 | Format("04/01/04", "y") | 92 | yy | Displays a two-digit year value from 00 to 99 that represents the date's year | Format("04/01/04", "yy") | 04 | yyyy | Displays a four-digit year value from 0100 to 9999 that represents the date's year | Format("04/01/04", "yyyy") | 2004 |
Table 5.9. String User-Defined FormatsFormat | Explanation | Example | Result |
|---|
@ | Displays existing character or space | Format("VBA", "@@@@@") | VBA (includes two spaces before the string) | & | Displays existing character or nothing | Format("VBA", "&&&&&") | VBA | < | Displays all characters in lowercase | Format("VBA", "<<<") | vba | > | Displays all character in uppercase | Format("vba", ">>>") | VBA |
|
