Date generating

[Sg1990]

Hi, I'm trying to find a way to generate a list of monthly dates between two specified dates, whilst taking into account that some months have 28/29/30/31 days. Cannot for the life of me work out how!

[offthelip]

you can do it with the following formula: the start date is in A1, put this formula into A2 then copy the formula down column A

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

[Logit]

.
How about ... enter START date in A1.

Click A1

Left click and hold the drag handle / lower right corner and drag down as far as you need to.

Unless of course you are attempting to do something completely different ? More info please.

[Sg1990]

Sorry, I'd like to do it via a command button and vba code if possible.

[mdmackillop]

Sub Test()
    Dim Dts, Strt, Endd, i
    Dts = InputBox("Start date, Months  e.g. 05/01/2017,6", , "05/01/2017,6")
    Strt = DateValue(Split(Dts, ",")(0)): Endd = Split(Dts, ",")(1)
    With ActiveCell
        .Resize(Endd + 1).NumberFormat = ("dd/mm/yy")
        .Value = DateValue(Strt)
        For i = 1 To Endd
            .Offset(i) = WorksheetFunction.EoMonth(Strt, i-1) + Day(Strt)
        Next i
    End With
End Sub

[Logit]

mdmackillop

Nice code !

[mdmackillop]

@ Logit
Thanks

[Sg1990]

Indeed a Nice bit of code! Unfortunately it doesn't take into account whether there are 28/29/30/31 days in the month.

Ideally I like to generate the following:
Start date 31/01/17
31/01/17
28(29)/02/17
31/03/17
30/04/17
Etc

[mdmackillop]

That was not clear from your question.

Sub Test()    Dim Dts, Strt, Endd, i
    Dts = InputBox("Start date, Months  e.g. 05/01/2017,6", , "05/01/2017,6")
    Strt = DateValue(Split(Dts, ",")(0)): Endd = Split(Dts, ",")(1)
    With ActiveCell
        .Resize(Endd).NumberFormat = ("dd/mm/yy")
        .Value = DateValue(Strt)
        For i = 0 To Endd - 1
            .Offset(i) = WorksheetFunction.EoMonth(Strt, i)
        Next i
    End With
End Sub