Different Ways to Format Dates in VBA


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.

Long Formats

  • 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.

Short 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.

Sample Program

  • 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.

    Sub FormatDates()
    Dim fmt, s1

    fmt = InputBox("Type a date format")
    s1 = Format(Now, fmt)
    MsgBox s1

    End Sub

Related Searches


  • "Excel 2010 Bible"; John Walkenback; 2010
  • "Mastering VBA for Office 2010 "; Richard Mansfield; 2010
Promoted By Zergnet


Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!