how to get month-a-versaries (month anniversaries)

I have a formula in excel which if I give today's date, it gives me the next month's date after 1 month. Say today is 04/20/2005, so the formula in excel gives me 05/20/2005, 06/20/2005….

Here is the formula: =DATE(IF(MONTH(D4) =12, (YEAR(D4)), (YEAR(D4))), MONTH(D4) +1, DAY(D4))

I am currently trying to come up with a function that will do just that given the beginning date. So in the sheet. When I have 14 months b/w the beginning and end date, it gives me all the monthaversaries .
There is a place for the beginning months and end month on the same sheet and since the beginning month and end month can can change, it can be 4 months or 15 months or more, but based on these two dates i want a macro that will give me the month- anniversaries.

Any help would be appreciated.

Good morning Mmamin

I don't fully understand what you're trying to do, but reading between the lines there is a formula that I think will help you, and will replace the longer formula that you are using. The function is =EDATE() and is not available until you invoke the Analysis ToolPak add-in (Tools > Addins)

All these cells must be formatted as dates:
If you have 24 April 2005 in A1 and the formula
=EDATE(A1,1)
in B1 will return 24 May 2005. If you put this formula in C1
=EDATE(C4,-1)
it will return 24 March 2005.

HTH

DominicB

Actually I was wondering how i could get it in VBA because as soon as I put the beginning month and end month on a cell in the worksheet, I want it to give me all the monthaversaries. Can you help with that.

Hi Mmamin

You haven't said exactly what you want to do with these dates once you have them, but I have a solution that will suffice if you don't want to do any calculations with them. I am English so work dd/mm/yy but Excel is American and VBA wants to insist on using mm/dd/yy, so that's the way the example works:

Sub Anniv()
MyDate = #4/25/2005#
MyMonth = Month(MyDate) + 1
MyDay = Day(MyDate)
MyYear = Year(MyDate)
NewDate = MyMonth & "/" & MyDay & "/" & MyYear
MsgBox NewDate
End Sub

As you can see, the routine takes a date, breaaks it into its component parts and reassembles it incrementing the month by one. It's not a great way to do it but it's the only way I can find at the moment. Someone out there may well have a better way...

HTH

DominicB