Generate dates from a starting date

[alice]

Input:
Starting Date: Jan 31, 2003
Number of Dates to generate: 5
----------------
Output expected:
Jan 31, 2003
Feb 28, 2003
Mar 31, 2003
Apr 30, 2003
May 31, 2003

How can I do this in excel using macros?

I already did a macro, but it only works for days between
1 to 28, b/c Feb 31, 2003 does not exist and it is not
recognized as a valid date format.

Please help!

 

[Bill James]

Seems to me that what you really need is a function that
returns the last day of month for a given month and year.

There is a function called EOMONTH() which is in the
Analysis Tool Pack Excel add-in which does this.

EOOMONTH(start_date,months)

Example:

Get last day of this month (October 2003)

=EOMONTH(Date(2003,10,18),0)

You'll have to look at the help file, but simply
translation of the above: Get the last day of the month
for Oct 18,2003 plus 0 months.

So, you can replace 0 with a variable to generate the
dates you need:

for n = 0 to 4
call EOMONTH(Date(2003,10,18),n)
next

 

[Tomm]

Or just plain VBA. DateSerial using 0 as Day argument will return number of
days in a month:

Sub GenerateDays()
Dim dtStart As Date
Dim lNumDates As Long
Dim l As Long
Dim lLastDay As Long
Dim dtNewDate As Date


dtStart = "Jan 31, 2003"
lNumDates = 5

dtStart = DateSerial(Year(dtStart), Month(dtStart), 1)
For l = 1 To lNumDates

dtNewDate = DateAdd("m", l - 1, dtStart)
lLastDay = Day(DateSerial(Year(dtNewDate), Month(dtNewDate) + 1, 0))
dtNewDate = DateSerial(Year(dtNewDate), Month(dtNewDate), lLastDay)
Debug.Print dtNewDate
Next


End Sub


Tomm