Populate all months between two dates

Question

I have been trying to make a formula in Excel that populates the month between two dates.

All i get is DatedIF() but it does not fulfil the requirments.

Here is the picture that i want from the Excel Formula. If i update any date in the start date or end date formula will automatically calculate the all months between the dates and give the result as available in attached picture.

Code result and the result i want. Code should work accordingly with the dates.

https://docs.google.com/spreadsheets/d/1AbLs8soPOgQvorwIzWLIJIooIQiVg9fvKaBdxY0tmgU/edit#gid=1340167497

=IF(ROW()=15,0,IFERROR(INDEX(MONTH(EDATE(Sheet1!B$4,ROW(A$15:INDEX(A:A,DATEDIF(Sheet1!B$4,Sheet1!G$1,"m"))))),COUNT(A$15:A15)),""))

Answer 1

You could try:

Formula in A4:

=IF(ROW()=4,0,IFERROR(INDEX(MONTH(EDATE(B$1,ROW(A$1:INDEX(A:A,DATEDIF(B$1,B$2,"m"))))),COUNT(A$3:A3)),""))

Drag down.

Note: With, for example, end-date being "30-12-2021" this will only work up to 11. If you would still want to see up to 12, you'd need to use EOMONTH() as a nested function in there.

Answer 2

This will populate from row five onwards. No doubt the code could be adapted to work as an array formula.

Sub Test()

    With ThisWorkbook.Worksheets("Sheet1")
        PopulateMonths .Range("B2"), .Range("B3")
    End With

End Sub

Sub PopulateMonths(FirstDate As Range, SecondDate As Range)

    Dim MonthCount As Long
    MonthCount = DateDiff("m", FirstDate, SecondDate)
    
    With ThisWorkbook.Worksheets("Sheet1")
    
        'This will clear from row 5, column 1 to the last piece of data in column 1.
        'If column 1 is already empty it will clear from A1:A5, so best to check if
        'there's any data to clear before this line runs (just see if A5<>"")
        .Range(.Cells(5, 1), .Cells(.Rows.Count, 1).End(xlUp)).ClearContents
    
        Dim x As Long
        For x = 0 To MonthCount
            .Cells(x + 5, 1) = x 'Start on row 5, column 1.
        Next x
    
    End With
    
End Sub

Answer 3

Populate Months Between Two Dates

• The following is an automated version which uses the Worksheet Change event i.e. it runs only if you manually or via VBA change the values (not by formula). You don't have to run anything just copy the codes to the appropriate modules and adjust the values in the constants section.
• If for some reason you don't want the automation, you can delete the code in the sheet module, delete the three constants below Option Explicit in the standard module and uncomment the three constants in populateMonths. Now it will only work with populateMonthsInit.
• If the start date is the last date of the month, the month will not be included. Similarly, if the end date is the first date of the month, the month will not be included. This can easily be adjusted.

Sheet Module e.g. Sheet1

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Intersect(Union(Range(StartDateCell), Range(EndDateCell)), Target)
    If Not rng Is Nothing Then
        populateMonths
    End If
End Sub

Standard Module e.g. Module1

Option Explicit
 
Public Const StartDateCell As String = "B1"
Public Const EndDateCell As String = "B2"
Private Const FirstCell As String = "B4"

Sub populateMonthsInit()
    populateMonths
End Sub

Sub populateMonths(Optional ws As Worksheet)
    'Const StartDateCell As String = "B1"
    'Const EndDateCell As String = "B2"
    'Const FirstCell As String = "B4"
    If ws Is Nothing Then
        Set ws = ThisWorkbook.ActiveSheet
    End If
    With ws.Range(FirstCell)
        Dim Data As Variant
        Data = getMonthNumbers(.Worksheet.Range(StartDateCell).Value, _
            .Worksheet.Range(EndDateCell).Value)
        .Resize(.Worksheet.Rows.Count - .Row + 1).ClearContents
        .Value = 0 ' Not sure what that's all about.
        If Not IsEmpty(Data) then
            .Offset(1).Resize(UBound(Data, 1)).Value = Data
        End if
    End With
End Sub

Function getMonthNumbers( _
    ByVal StartDate As Date, _
    ByVal EndDate As Date) _
As Variant
    On Error GoTo clearError
    Dim Months As Long: Months = DateDiff("m", StartDate, EndDate)
    Dim StartMonth As Long
    If Month(StartDate + 1) = Month(StartDate) Then
        Months = Months + 1
        StartMonth = modMonth(Month(StartDate))
    Else
        StartMonth = modMonth(Month(StartDate) + 1)
    End If
    If Month(EndDate - 1) <> Month(EndDate) Then
        Months = Months - 1
    End If
    Dim Data() As Long: ReDim Data(1 To Months, 1 To 1)
    Data(1, 1) = StartMonth
    Dim i As Long
    For i = 2 To Months
        Data(i, 1) = modMonth(Data(i - 1, 1) + 1)
    Next i
    getMonthNumbers = Data
ProcExit:
    Exit Function
clearError:
    Resume ProcExit
End Function

Function modMonth( _
    m As Long) _
As Long
    modMonth = IIf(m Mod 12, m Mod 12, 12)
End Function

Sub TESTgetMonthNumbers()
    Dim Data As Variant: Data = getMonthNumbers(Range("B1"), Range("B2"))
    If Not IsEmpty(Data) Then
        Debug.Print Join(Application.Transpose(Data), vbLf)
    Else
        Debug.Print "Nope."
    End If
End Sub