VBA Format Date

This tutorial will demonstrate how to format dates using VBA.

Format Dates in VBA

There are two methods to format dates using VBA.

1. The NumberFormat property of Cells / Ranges – To format dates stored in cells
2. The VBA Format function – To format dates in VBA (ex. variables)

NumberFormat – Dates

The default number format of a cell in Excel is General. You can display values as numbers, text, dates, percentages or currencies by changing the number format. The NumberFormat property can be used in VBA to set the number format of dates in a cell or range.

Note: In order to see the different standard date formats available in Excel go to Home>Number and you’ll see options such as Short Date, Long Date and Time.

Short Date

The Short date number formatting option displays the date in a short, numerical format.

The following code will set the .NumberFormat property of cell A1 to Short date:

Range("A1").NumberFormat = "mm/dd/yyyy"

Long Date

Long date number formatting displays the date in a longer, written format. The following code will set the .NumberFormat property of cell A1 to Long date:

Range("A1").NumberFormat = "dddd, mmmm dd, yyyy"

Custom dates

In order to see the custom number format codes that you can use in VBA to format dates, go to Home>Number and click the Dialog Box Launcher.  Select the Number tab and choose Custom.

You can either select the custom built-in formats for your date or create your own user-defined date formats. The following code will set the .NumberFormat property of cell A1 to a built-in custom date format:

Range("A1").NumberFormat = "mmm-yy"

The result is:

The following code will set the .NumberFormat property of cell A1, to a user-defined custom date format:

Range("A1").NumberFormat = "dddd-dd-mmm-yy"

The result is:

By reviewing Excel’s pre-built examples you can learn how NumberFormats should be entered. For more information, read Microsoft’s documentation on number formats for dates.

VBA Format Function

As mentioned above, the NumberFormat method is appropriate for setting the Numberformat of dates stored in Excel cells.  In VBA, you can use the Format Function to convert dates to strings with certain date formatting.

You would use the following syntax to format dates:

Format(String_Representation, NumberFormatCode) where:

String_Representation – the text string representing the date.

NumberFormatCode – the number format code specifying the way the date should be displayed.

The following code shows how to format a text string representation of a date as long date format:

MsgBox Format("1/1/2010", "dddd, mmmm dd, yyyy")

The result is:

 

Notice that the Format Function uses the same date formatting syntax as the NumberFormat above.

The following code shows how to format a text string representation of a date as medium date format:

MsgBox Format("09 October 2012", "Medium Date")

The result is:

The following code shows how to format a text string representation of a date as a user-defined format:

MsgBox Format("09 October 2012", "dddd: dd/mm/yy")

VBA Custom Format Builder

Our VBA Add-in: AutoMacro contains a Custom Format Builder for the VBA Editor. This allows you to set custom formats and immediately preview the output for your desired value:

The VBA Add-in contains numerous other “Code Generators”, an extensive code library, and an assortment of other coding tools.  It’s the ultimate add-in for anyone serious about VBA programming!

VBA Format Date in Access

The VBA Format Date function work exactly the same in Access VBA as in Excel VBA.

Function GetDateFilter() As String
'create a string to get information from 2 date fields on a form in Access
   Dim strDateField As String
   If IsNull(Me.txtSDate) = False Then
      If IsNull(Me.txtEDate) = True Then Me.txtEDate = Me.txtSDate
      If strDateField2 = "" Then
        GetDateFilter = strDateField & " Between #" & Format(Me.txtSDate, "mm/dd/yyyy") & "# And # " & Format(Me.txtEDate, "mm/dd/yyyy") & "#"
      End If
   End If
End Function