Paste the month an year in cells

[Peterso]

In Sheet 1 cell(1,1) I have a date, say 2-Jul-1998. And in cell(2,1) I have another date, say 24-Feb-2024. I want to put in Sheet(2), cell(4,4).value=Jul-1998, cells(4,5).value=Aug-1998.......Cells(4,k).value=Feb-2024. how to write the macro? Thanks

 

[Rick Rothstein]

What version of Excel are you using? ...which, by the way, you should add to your profile.

 

[Peterso]

I am using excel 2021

 

[kvsrinivasamurthy]

In Cell(4,4) of sheet2

=LET(a,Sheet1!A1,b,Sheet1!A2,DATE(YEAR(a),SEQUENCE(YEAR(b)*12+MONTH(b)+1-YEAR(a)*12-MONTH(a),1,MONTH(a),1),1))

Format result cells for Mmm-yy

 

[Peterso]

In Cell(4,4) of sheet2

=LET(a,Sheet1!A1,b,Sheet1!A2,DATE(YEAR(a),SEQUENCE(YEAR(b)*12+MONTH(b)+1-YEAR(a)*12-MONTH(a),1),1))

Let me try. Thanks a lot!

 

[Rick Rothstein]

I thought you said you wanted a macro? If that is still the case, here is one. Note that the macro will automatically handle formatting the cells for you.

Sub SequenceMonthYearBetweenDates()
  Dim X As Long, Arr As Variant
  With Sheets("Sheet1")
    ReDim Arr(1 To 1, .Range("A2").Value - .Range("A1").Value + 1)
    For X = 1 To .Range("A2").Value - .Range("A1").Value + 1
      Arr(1, X) = WorksheetFunction.EoMonth(X + .Range("A1").Value - 1, 0)
    Next
  End With
  Arr = WorksheetFunction.Unique(Arr, 1)
  With Sheets("Sheet2").Range("D4").Resize(, WorksheetFunction.CountA(Arr))
    .Value = Arr
    .NumberFormat = "mmm-yyyy"
  End With
End Sub

However, if a formula solution is okay (you will have to format the cells manually), then give this a try...

=LET(a,Sheet1!A1,b,Sheet1!A2,UNIQUE(EOMONTH(SEQUENCE(,b-a+1,a),0),1))