The Format Function

The Format function converts a number or date to a string, using a number format supplied in code. The number format must use U.S. symbols (m, d, s, and so on), but results in a string formatted according to WRS (with the correct decimal, thousands, and date separators) and the WRS language (for the weekday and month names). For example, the following code will result in Friday 05/01/2007 in the UK, but Fredag 05.01.2007 when used with Norwegian settings:

MsgBox Format(DateSerial(2007, 1, 5), "dddd dd/mm/yyyy")

If you omit the number format string, it behaves in exactly the same way as the CStr function (even though online help says it behaves like Str), including the strange handling of Boolean values, where Format(True) always results in the English "True". Note that it does not change the date order returned to agree with the WRS, so your code has to determine the date order in use before creating the number format string, using Application.International(xlDateOrder). If the preceding code is run in the U.S., it returns the same as in the UK—Friday 05/01/2007—but your users will interpret that to mean May 1, not the correct January 5.

0 0

Post a comment