One way you can format dates with VBA is to use a series of "m" characters to indicate the long names of months. For example, the format "mmmm" will yield just the spelled out text "December," if the date you're formatting is in December. If you want a three-letter month, use "mmm," as in "Selection.NumberFormat = "mmm dd, yyyy"" for dates like "Dec. 31, 1966." Notice the use of the "d" character to represent the day.
Different ways of formatting dates in VBA include those for creating date strings with spelled out months, formatting with just numbers, with weekdays, and with customized formats. You can see the preset date formats available in VBA by using Excel's standard interface. Click the small arrow at the lower right of the "Home" tab's "Number" panel to display a list of these formats.
If you want numbers instead of spelled out text for your dates, use just one or two formatting characters for your months. For example, type "Selection.NumberFormat = "mm/dd/yyyy"" for dates like "12/31/1966." For "12/66," use the formatting string "mm/yy," or just "m/y."
Dates with Weekday
To format a date with a spelled out day, use four "d" characters to indicate the day. For example, type "Selection.NumberFormat ="dddd, mmm dd, yyyy"" for dates like "Saturday, December 31, 1966." To abbreviate the day without using numbers, use three "d" characters instead of four, as in "ddd, mmm dd, yyyy."
Custom Date Formats
You can create a customized date format in VBA by using any delimiter besides the common ones of "/," and "-." For example, you can use a statement like "Selection.NumberFormat = "mm~dd~yy"" to produce dates such as "12~31~66." You may choose such a custom format just for the sake of style, or to prepare your date strings for programs that require specific characters for their input.
The following sample VBA program lets you quickly try out different date formats. Paste the program into the VBA programming environment, which you can enter by pressing "Alt" and "F11" simultaneously after opening Excel. After running the program by pressing "F5," type a date format when prompted, such as "mm/dd/yy." The program will display the current date in the format you specified.
Dim fmt, s1
fmt = InputBox("Type a date format")
s1 = Format(Now, fmt)
- "Excel 2010 Bible"; John Walkenback; 2010
- "Mastering VBA for Office 2010 "; Richard Mansfield; 2010