Add 18 months

[Debbie]

If DOB is in a1; how would you add 18 months to that DOB?
Thanks!

 

[JP Ronse]

Hi Debbie

=DATE(YEAR(J2);MONTH(J2)+18;DAY(J2))

With J2 = DOB

Note that my locale use ";" instead of ",". You might have to change this.

Wkr,

JP

 

[barry houdini]

or you can use EDATE function, which is an inbuilt function in Exce
2007 but an Analysis ToolPak function in earlier versions, i.e.

=EDATE(A1,18)

Note: that won't alway give the same result as JP Ronse's suggestion
If A1 is 31st August 2009 then EDATE will return 28th Feb 2011 bu
using

=DATE(YEAR(A1),MONTH(A1)+18,DAY(A1))

will return 3rd March 201

 

[Debbie]

Hi Debbie

=DATE(YEAR(J2);MONTH(J2)+18;DAY(J2))

With J2 = DOB

Note that my locale use ";" instead of ",". You might have to change this..

Wkr,

JP






- Show quoted text -

Awesome, thank you! Works great.

 

[macropod]

Hi Debbie,

The safest formula is:
=MIN(DATE(YEAR(A1),MONTH(A1)+18+{0,1},DAY(A1)*{1,0}))
With this, if the starting month has more days than the end month, and the start day is on the last day of the month, the returned
date will be the last day of the month that's 18 months hence. For example, if the start date is 31/August/2008, the returned date
would be 28/February/2010.

However, a formula like:
=DATE(YEAR(A1),MONTH(A1)+18,DAY(A1))
is liable to return a date in the following month in such circumstances. For example, if the start date is 31/August/2008, the
returned date would be 3/March/2010.

As barry houdini says, you can use EDATE function, but this requires the Analysis ToolPak to be activated in versions before Excel
2007.