software

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(YE AR(A1),MONTH(A1) 2 1,0))))

If you have the Analysis Toolpak loaded, Toolsgt;Addinsgt;Analysis Toolpak,
then you could use the function
=EDATE(A1,2)

--
Regards

Roger Govierquot;thirumalairajanquot; gt; wrote in message
...
gt; Dear friends,
gt;
gt; In an excel worksheet, in a cell I am having a day, say for example
gt; 03-May-2005 or 03-05-2005, In another cell
gt; I have to increment only the month by 2 i.e from 03-May-2005 to
gt; 03-July-2005 using a formula.
gt;
gt; What formula should I use?
gt;
gt; thanks,
gt; Thirumalairajan.
gt;
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)

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

=DATE(YEAR(A3),MONTH(A3) 2,DAY(A3))

where A3 contains the date that you want to add 2 months--
mphell0
------------------------------------------------------------------------
mphell0's Profile: www.excelforum.com/member.php...oamp;userid=30153
View this thread: www.excelforum.com/showthread...hreadid=510939
This may depend on what result you want. If your original date (in cell
A1) is
31st December 2005 then

=DATE(YEAR(A1),MONTH(A1) 2,DAY(A1))

will give you a result of 3rd March 2006

whereas

=EDATE(A1,2)

will give you

28th February 2006

note EDATE is part of Analysis ToolPak add-in--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=510939
Bob Phillips Wrote:
gt; Another way
gt;
gt; =MIN(DATE(YEAR(A1),MONTH(A1) {2,3},DAY(A1)*{1,0}))
gt;
gt; which is an array formula, it should be committed with
gt; Ctrl-Shift-Enter, not
gt; just Enter.
gt;
gt;

Bob, my understanding is that this formula only requires enter,
certainly works OK for me that way--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=510940