VBA UDF for list of MM.YYYY (months) between two dates

March 28, 2022

I am trying to list the dates between two given months: a) 1/1/2021; b) 6/1/2021 in format:
01.2021; 02.2021; 03.2021; 04.2021; 05.2021; 06.2021
I was able to find and use this UDF:

Function MONTHRANGE(startDate As Date, endDate As Date, _
                                Optional Delim As String = "; ", _
                                Optional dFormat As String = "MM.YYYY") As String
                                
MONTHRANGE = Join(Evaluate("TRANSPOSE(TEXT(ROW(" & CLng(startDate) & ":" & CLng(endDate) & ")," & Chr(34) & dFormat & Chr(34) & "))"), Delim)
End Function

The output of this is repeated dates (for each day of the month) in the format I want – how can I return just the unique values (one – per month)?

>Solution :

Something like the following gets the job done:

Option Explicit

Private Sub Test()
   Debug.Print GetMonths(CDate("1/1/2021"), CDate("6/1/2021"))
End Sub

Private Function GetMonths(ByVal StartDate As Date, ByVal EndDate As Date) As String
   Do While StartDate <= EndDate
      GetMonths = GetMonths & Format(Month(StartDate), "00") & "." & Year(StartDate) & "; "
      StartDate = DateAdd("m", 1, StartDate)
   Loop
   
   GetMonths = Left(GetMonths, Len(GetMonths) - 2)
End Function