Problems with adding dates

nicolehalliday

Board Regular
Joined
May 19, 2010
Messages
56
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:

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

and

Code:
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!
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
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

Board Regular
Joined
May 19, 2010
Messages
56
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top