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

---

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 .