PDA

View Full Version : Date generating



Sg1990
04-14-2017, 03:20 PM
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
04-14-2017, 03:55 PM
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
04-14-2017, 05:14 PM
.
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
04-15-2017, 12:35 AM
Sorry, I'd like to do it via a command button and vba code if possible.

mdmackillop
04-15-2017, 03:41 AM
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
04-15-2017, 04:38 AM
mdmackillop (http://www.vbaexpress.com/forum/member.php?87-mdmackillop)

Nice code !

mdmackillop
04-15-2017, 05:04 AM
@ Logit
Thanks

Sg1990
04-15-2017, 05:48 AM
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
04-15-2017, 07:21 AM
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