Count # of months between two dates and add rows

[reltub]

I am trying to figure out how to code some VBA that counts the number of months between two dates (stored in rows), add that many rows minus 1 between the two rows, and increment the date by month in subsequent rows. I will attach some files for reference. The first is the original sheet. The second is what I want to end up with. Thanks for your help!!

test1.xlsx
test2.xlsx

[p24leclerc]

try this macro:

Please Login or Register  to view this content.

Hope this helps.
Regards

[reltub]

Pierre -

Works like a charm! Thank you very much!! However, if the month has 31 days, I run into a problem. It will think it's the beginning of the next month, and skip a month. Can I change No_Month to fix this? Perhaps use a different method to calculate the days in a month?

66285WHD1 10/31/2031 64.104
66285WHD1 12/1/2031 64.104
66285WHD1 1/1/2032 64.104
66285WHD1 2/1/2032 64.104
66285WHD1 3/1/2032 64.104
66285WHD1 4/1/2032 64.104
66285WHD1 5/1/2032 64.104
66285WHD1 6/1/2032 64.104
66285WHD1 7/1/2032 64.104
66285WHD1 8/1/2032 64.104
66285WHD1 9/1/2032 64.104
66285WHD1 10/1/2032 64.104

[p24leclerc]

Go ahead. You can change this formula to suit your needs. I thought your date would all start with the beginning of the month.
What would you like to get as date after 10/31/2031? 11/01/2031?

[reltub]

Logically, I would need it to realize that 10/31 is the end of October instead of the beginning of November. I guess that's why you assumed a 30 day month, since if I had 10/31/2031, 11/31/2012 wouldn't make any sense. How could I alter the vba to recognize 10/31/2012 as the end of October, instead of the beginning of November, then alter subsequent dates based on how many days are in that month? See below:

10/31/2031
11/30/2031
12/31/2031
01/31/2032
02/28/2032
03/30/2032 and so on....

Again, this would only happen if the stated date is 31, or perhaps in February where there aren't 30 days.

[p24leclerc]

Do you mean you'll never have dates like 11/24/2031?
If you do, what would be the sequence?
11/24/2031
12/24/2031
1/24/2032
???

[reltub]

Yes, it could be any date in any month in any year. If I had 11/24/2031 in cell A1, the sequence would be:

11/24/2031
12/24/2031
01/24/2032 and so on...

The macro works perfectly for all dates except where the day is 31. For example, if A1 is 10/31/2031, the sequence would need to be:

10/31/2031
11/30/2031
12/31/2031
01/31/2032
02/28/2032
03/30/2032 and so on....

Currently, excel is thinking if the day is 31, then that is the beginning of the next month, so the progression looks like this:

10/31/2031
12/01/2031
01/01/2032
02/01/2032 and so on...

[p24leclerc]

Here is a new approach for this problem.
the macro creates new row as it goes through the date from the top down.

Please Login or Register  to view this content.

I think it does a pretty good job.
Regards

[reltub]

Works great except for one minor detail; there are duplicate dates. I need to keep the date and value on the bottom in the screenshot. What's the best way to do this? Remove duplicates, since it's looping from bottom to top?

test1.jpg

[p24leclerc]

I'll look at the duplicates issue in details. What seems to be a quick fix for the moment is to change the number 30 with 31 on this lin:

Please Login or Register  to view this content.

I'm not sure to understand your other issue if any. Please advise?