Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel formula to increment the month by 2?
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, ToolsAddinsAnalysis Toolpak, then you could use the function =EDATE(A1,2) -- Regards Roger Govier "thirumalairajan" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel formula to increment the month by 2?
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" wrote in message ... 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, ToolsAddinsAnalysis Toolpak, then you could use the function =EDATE(A1,2) -- Regards Roger Govier "thirumalairajan" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel formula to increment the month by 2?
=DATE(YEAR(A3),MONTH(A3)+2,DAY(A3)) where A3 contains the date that you want to add 2 months -- mphell0 ------------------------------------------------------------------------ mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153 View this thread: http://www.excelforum.com/showthread...hreadid=510939 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel formula to increment the month by 2?
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: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=510939 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Excel formula to increment the month by 2?
Bob Phillips Wrote: 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 -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=510940 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions |