Excel formula to increment the month by 2?

Dear friends,

In an excel worksheet, in a cell I am having a day, say for example
03-May-2005 or 03-05-2005, In another cell
I have to increment only the month by 2 i.e from 03-May-2005 to
03-July-2005 using a formula.

What formula should I use?

thanks,
Thirumalairajan.

Hi

=DATE(YEAR(A1),MONTH(A1)+2,DAY(A1)) gives the correct result, most of
the time.

It is incorrect if you had 29, 30 or 31 December in A1, as it would give
dates in March rather than February for all three dates when the result
is a non-leap year, and for the latter 2 when the result is a leap year
The following formula, posted by the late Frank Kabel, corrects this
issue
=DATE(YEAR(A1),MONTH(A1)+2,MIN(DAY(A1),DAY(DATE(YEAR(A1),MONTH(A1)+2+1,0))))

If you have the Analysis Toolpak loaded, Tools>Addins>Analysis Toolpak,
then you could use the function
=EDATE(A1,2)

--
Regards

Roger Govier


"thirumalairajan" <[email protected]> wrote in message
news:Oqk1%[email protected]...
> Dear friends,
>
> In an excel worksheet, in a cell I am having a day, say for example
> 03-May-2005 or 03-05-2005, In another cell
> I have to increment only the month by 2 i.e from 03-May-2005 to
> 03-July-2005 using a formula.
>
> What formula should I use?
>
> thanks,
> Thirumalairajan.
>

Another way

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

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Roger Govier" <[email protected]> wrote in message
news:#[email protected]...
> Hi
>
> =DATE(YEAR(A1),MONTH(A1)+2,DAY(A1)) gives the correct result, most of
> the time.
>
> It is incorrect if you had 29, 30 or 31 December in A1, as it would give
> dates in March rather than February for all three dates when the result
> is a non-leap year, and for the latter 2 when the result is a leap year
> The following formula, posted by the late Frank Kabel, corrects this
> issue
>
=DATE(YEAR(A1),MONTH(A1)+2,MIN(DAY(A1),DAY(DATE(YEAR(A1),MONTH(A1)+2+1,0))))
>
> If you have the Analysis Toolpak loaded, Tools>Addins>Analysis Toolpak,
> then you could use the function
> =EDATE(A1,2)
>
> --
> Regards
>
> Roger Govier
>
>
> "thirumalairajan" <[email protected]> wrote in message
> news:Oqk1%[email protected]...
> > Dear friends,
> >
> > In an excel worksheet, in a cell I am having a day, say for example
> > 03-May-2005 or 03-05-2005, In another cell
> > I have to increment only the month by 2 i.e from 03-May-2005 to
> > 03-July-2005 using a formula.
> >
> > What formula should I use?
> >
> > thanks,
> > Thirumalairajan.
> >
>
>

Originally Posted by Bob Phillips

Another way

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

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Bob, my understanding is that this formula only requires enter, certainly works OK for me that way