Need to add 18 months to a date

J

John C

If you have the analysis toolpack add-in installed:
A1: your date
B1: =EDATE(A1,18)

If not
B1: =DATE(YEAR(A1),MONTH(A1)+18,DAY(A1))

The advantage of the EDATE function is if your day of the month is the 31st,
and 18 months later is not a 31 day month, it will return the end of month
for 18 months later. i.e: A1=08/31/08 then B1=02/28/10
OPT1: A1=08/31/08 then B1=02/28/10
OPT2: A1=08/31/08 then B1=03/03/10

Hope this helps!
 
S

Sandy Mann

John,
The advantage of the EDATE function is if your day of the month is the
31st,
and 18 months later is not a 31 day month, it will return the end of month
Click to expand...

Your 2nd formula can be modified to return the last day of the month by
changing it to:

=MIN(DATE(YEAR(A1),MONTH(A1)+{18,19},DAY(A1)*{1,0}))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
B

Bob Phillips

So does this

=MIN(DATE(YEAR(A1),MONTH(A1)+{19,18},DAY(A1)*{0,1}))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Top