Increment Date Until

[ncalenti]

Here is something tricky I am trying to implement without VBA if possible.

So I have 2 dates and a number of months.
Ex
D1 = 4/1/2006
D2 = 5/1/2009
Months = 18

Here is the algorithm in english:
Do until D1>=D2:
1. Add Number of months to D1
2. After that, keep incrementing D1 by a year

So running through the example:
Step 1 : Get D1 - 4/1/2006
2: Add Months to it - 4/1/2006 + 18 months = 10/1/2007
3: Test 10/1/2007 >= 5/1/2009 = False.
4: Add 1 year onto 10/1/2007 = 10/1/2008
5. Still not >= 5/1/2009
6. Add another year = 10/1/2009
7. Now it bigger so return 10/1/2009

From an efficiency standpoint, keep in mind calc needs to run on > 300k rows

 

[tigeravatar]

ncalenti,

=IF(A2<B2,MONTH(A2)+MOD(C2,12)&"/"&DAY(A2)&"/"&YEAR(A2)+YEAR(B2)-YEAR(A2)+INT(C2/12),B2)
Give the below formula a try. Notes:I assumed there were headers, so data and formula start on row 2
A2 = Date1
B2 = Date2
C2 = Months


=IF(A2<B2,MONTH(A2)+MOD(C2,12)&AMP;" p ?&YEAR(A2)+YEAR(B2)-YEAR(A2)+INT(C2 ?&DAY(A2)&? 12),B2)<>
Hope that helps,
~tigeravatar

 

[ncalenti]

Can you try reposting formulas. I dont think they pasted right. Thanks

 

[tigeravatar]

Weird, the only way I could get it to post was to put spaces around the < symbol. So make sure to take those spaces out:

=if(a2 < b2,month(a2)+mod(c2,12)&"/"&day(a2)&"/"&year(a2)+year(b2)-year(a2)+int(c2/12),b2)

 

[ncalenti]

another note: Seeing as its friday, and I want to get this done, if anyone has a VBA solution, I am all ears!

 

[jasonb75]

D3 holds Months in the sample formula

=EDATE(D1,CEILING(DATEDIF(D1,D2,"M")-D3,12)+D3)