If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
how to calculate x months from today
Excel 2003: I have a "date" in a cell (eg: 6/16/09), and want to calculate
the date which is 6 months from now? The issue I'm having is that some months are 29, 30, 31 days. The "date" call value may change and the "x" months period could also change. Hope this is clear. THANKS! Gene |
#2
|
|||
|
|||
how to calculate x months from today
Hi,
Try =DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)) date in a1 and months to add in b1 Mike "Gene" wrote: Excel 2003: I have a "date" in a cell (eg: 6/16/09), and want to calculate the date which is 6 months from now? The issue I'm having is that some months are 29, 30, 31 days. The "date" call value may change and the "x" months period could also change. Hope this is clear. THANKS! Gene |
#3
|
|||
|
|||
how to calculate x months from today
Months are such a terrible unit to perform math with. As an example, if you
add 6 months to the date August 31, 2009, what date do you expect to have returned to you? -- Rick (MVP - Excel) "Gene" wrote in message ... Excel 2003: I have a "date" in a cell (eg: 6/16/09), and want to calculate the date which is 6 months from now? The issue I'm having is that some months are 29, 30, 31 days. The "date" call value may change and the "x" months period could also change. Hope this is clear. THANKS! Gene |
#4
|
|||
|
|||
how to calculate x months from today
"Gene" wrote:
Excel 2003: I have a "date" in a cell (eg: 6/16/09), and want to calculate the date which is 6 months from now? The issue I'm having is that some months are 29, 30, 31 days. Try EDATE(A1,A2), where A1 is the original date and A2 is the number of months. You may need to explicitly format the cell as Date or whatever Custom date format you wish. If you get a #NAME error, see the EDATE help page. If you are unable or unwilling to install the Analysis ToolPak, it is possible to do this with EDATE. Try: =min(date(year(A1),A2+month(A1),day(A1)), date(year(A1),1+A2+month(A1),0)) |
#5
|
|||
|
|||
how to calculate x months from today
Maybe better illustrated using this example:
A1 = 1/31/2009 What date should be returned after adding 1 month to the date in cell A1? There is no 2/31/2009 so what date should be returned? -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... Months are such a terrible unit to perform math with. As an example, if you add 6 months to the date August 31, 2009, what date do you expect to have returned to you? -- Rick (MVP - Excel) "Gene" wrote in message ... Excel 2003: I have a "date" in a cell (eg: 6/16/09), and want to calculate the date which is 6 months from now? The issue I'm having is that some months are 29, 30, 31 days. The "date" call value may change and the "x" months period could also change. Hope this is clear. THANKS! Gene |
#6
|
|||
|
|||
how to calculate x months from today
Hi,
With the date as 1/31/2009 and B1 holding 1, your formula would return 3/3/2009 whereas the EDATE function would return 2/28/2009. I wonder which one is correct. In your formula, B1 is interpreted as 31 days and therefore the answer is 3/3/2009. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mike H" wrote in message ... Hi, Try =DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)) date in a1 and months to add in b1 Mike "Gene" wrote: Excel 2003: I have a "date" in a cell (eg: 6/16/09), and want to calculate the date which is 6 months from now? The issue I'm having is that some months are 29, 30, 31 days. The "date" call value may change and the "x" months period could also change. Hope this is clear. THANKS! Gene |
#7
|
|||
|
|||
how to calculate x months from today
On Tue, 16 Jun 2009 13:54:54 -0700, "JoeU2004" wrote:
"Gene" wrote: Excel 2003: I have a "date" in a cell (eg: 6/16/09), and want to calculate the date which is 6 months from now? The issue I'm having is that some months are 29, 30, 31 days. Try EDATE(A1,A2), where A1 is the original date and A2 is the number of months. You may need to explicitly format the cell as Date or whatever Custom date format you wish. If you get a #NAME error, see the EDATE help page. If you are unable or unwilling to install the Analysis ToolPak, it is possible to do this with EDATE. Try: =min(date(year(A1),A2+month(A1),day(A1)), date(year(A1),1+A2+month(A1),0)) Same algorithm, a bit shorter: =MIN(DATE(YEAR(A1),MONTH(A1)+A2+{1,0},DAY(A1)*{0,1 })) --ron |
Thread Tools | |
Display Modes | |
|
|