Problems with adding dates

[nicolehalliday]

Hello,

I am having trouble with dates in my macro. The macro will add one month to a date and return the future date in another cell. The problem is that I want the date to be the exact same (eg. May 14 and then June 14), rather than adding 30 days. All the methods I've tried have just added 30 days so the day is not the same number. I also realize I need to account for certain dates, such as there is Jan 30th but no Feb 30 - in this case I would want to take Feb 28. Here is the code that I've tried so far:

                          divarray(y, 1) = DateAdd("m", 1, lastdate)
                          divarray(y, 7) = DateAdd("m", 1, lastpaydate)
                          divarray(y, 8) = DateAdd("m", 1, recdate)

and

divarray(y, 1) = "=date(year(" & divarray(x, 1) & "),month(" & divarray(x, 1) & ")+1,day(" & divarray(x, 1) & ")"
                divarray(y, 7) = "=date(year(" & divarray(x, 7) & "),month(" & divarray(x, 7) & ")+1,day(" & divarray(x, 7) & ")"
                divarray(y, 8) = "=date(year(" & divarray(x, 8) & "),month(" & divarray(x, 8) & ")+1,day(" & divarray(x, 8) & ")"

I'd also like to give a big thanks to everyone who spends time on Mr. Excel replying to posts. You've helped me so much and I've learned a lot, thank you!

 

[theBardd]

What makes you think DateAdd will add 30 days? It adds a month, so 30th May becomes 30th June, even though May has 31 days. And it works as you want with months such as Jan/Feb.

 

[nicolehalliday]

Thanks for the reply. It's not what I think - the macro is adding 30 days when I run the code using DateAdd. For example, if the reference date is June 15th, 2012 the dates I get are:

15-Jul-12</SPAN>

14-Aug-12</SPAN>

13-Sep-12</SPAN>

13-Oct-12</SPAN>

12-Nov-12</SPAN>

12-Dec-12</SPAN>

11-Jan-13</SPAN>

10-Feb-13</SPAN>

12-Mar-13</SPAN>

11-Apr-13</SPAN>

11-May-13</SPAN>

<TBODY>
</TBODY>

What's weird is that the other formula I was using (=date(year,month,day)) works fine when I enter it in the spreadsheet manually, however the macro will return +30 days. I need the macro so I can adjust for provisions such as less days in Feb.

Thanks