VBA: How to Use mm/dd/yyyy as Date Format


You can use the NumberFormat property in VBA to format date values using a mm/dd/yyyy format.

Here is one common way to do so in practice:

Sub FormatDates()

Dim i As Integer

For i = 2 To 11
    Range("A" & i).NumberFormat = "mm/dd/yyyy"
Next i

End Sub

This particular macro will format each of the dates in the range A2:A11 to have a mm/dd/yyyy format.

The following example shows how to use this syntax in practice.

Example: How to Use mm/dd/yyyy as Date Format in VBA

Suppose we have the following list of dates in Excel:

Suppose we would like to format each of the dates in column A using a mm/dd/yyyy format.

We can create the following macro to do so:

Sub FormatDates()

Dim i As Integer

For i = 2 To 11
    Range("A" & i).NumberFormat = "mm/dd/yyyy"
Next i

End Sub

When we run this macro, we receive the following output:

Notice that each of the dates in column A now uses a mm/dd/yyyy format.

Note that by using mm and dd in the format, we force all months and days to be shown with two digits.

For example, the date of March 3rd, 2023 is shown as 03/01/2023.

If you’d like, you could instead use m/d/yyyy as the format to only show one digit if the day or month happens to only be one digit:

Sub FormatDates()

Dim i As Integer

For i = 2 To 11
    Range("A" & i).NumberFormat = "m/d/yyyy"
Next i

End Sub

When we run this macro, we receive the following output:

The dates are now formatted to only show the minimum number of digits needed to accurately represent the days and months.

For example, the date of March 3rd, 2023 is shown as 3/1/2023.

Note: You can find the complete documentation for the NumberFormat property in VBA here.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

How to Compare Dates in VBA
How to Convert String to Date in VBA
How to Calculate Days Between Two Dates in VBA

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *