You can use the NumberFormat property in VBA to get the month and year from a date.

Here is one common way to use this property in practice:

Sub GetMonthYear()

Dim i As Integer

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

End Sub

This particular macro finds the month and year for each date in the range A2:A11 and displays these values in the corresponding cells in the range C2:C11.

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

Example: Add Days to Date in VBA

Suppose we have the following dataset in Excel that contains information about the sales made by some company on various dates:

Suppose we would like to get the month and year for each cell in the date column.

We can create the following macro to do so:

Sub GetMonthYear()

Dim i As Integer

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

End Sub

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

Notice that column C contains the month and year of each corresponding date in column A.

Note that we used the NumberFormat property to format the dates with two digits for the months and four digits for the years.

We could choose to display these values in a different format if we’d like.

For example, we could use the following syntax to display the month with only one digit (if the month only contains one digit) and the year with two digits:

Sub GetMonthYear()

Dim i As Integer

For i = 2 To 11
    Range("C" & i).Value = DateValue(Range("A" & i))
    Range("C" & i).NumberFormat = "m/yy"
Next i

End Sub

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

Feel free to play around with the different formatting options to display the month and year values however you’d like.