Need to add 18 months to a date

need to add 18 months to a date

johnc
Fri Jul 25 11:14:22 CDT 2008

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!
--
John C


"cdcam" wrote:

> need to add 18 months to a date

Sandy
Fri Jul 25 11:45:52 CDT 2008

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

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"John C" <johnc@stateofdenial> wrote in message
news:5567E5FF-AEFD-4E48-8B06-0C7F1F89EDDC@microsoft.com...
> 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!
> --
> John C
>
>
> "cdcam" wrote:
>
>> need to add 18 months to a date
>

Bob
Fri Jul 25 11:50:17 CDT 2008

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)

"John C" <johnc@stateofdenial> wrote in message
news:5567E5FF-AEFD-4E48-8B06-0C7F1F89EDDC@microsoft.com...
> 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!
> --
> John C
>
>
> "cdcam" wrote:
>
>> need to add 18 months to a date