Adding Months to Dates to create new value
I have a column that contains various dates (xx/xx/xxxx) In a column next to
this one I would like to add various # months to come up with a new date 8/1/2006 + 6months = 2/1/2007 Is this possible with formula? Thanks |
Adding Months to Dates to create new value
=(DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))) will give the answer you asked for in
that case, but the question in this type of problem is what you want as an answer when you add 6 months to a date like 30 Aug 06. -- David Biddulph "simond" wrote in message ... I have a column that contains various dates (xx/xx/xxxx) In a column next to this one I would like to add various # months to come up with a new date 8/1/2006 + 6months = 2/1/2007 Is this possible with formula? Thanks |
Adding Months to Dates to create new value
On Thu, 1 Mar 2007 22:40:15 -0800, simond
wrote: I have a column that contains various dates (xx/xx/xxxx) In a column next to this one I would like to add various # months to come up with a new date 8/1/2006 + 6months = 2/1/2007 Is this possible with formula? Thanks You can use the EDATE function. With 8/1/2006 in A1, =EDATE(A1,6) From HELP: Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue. If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. How? On the Tools menu, click Add-Ins. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK. If necessary, follow the instructions in the setup program. Syntax EDATE(start_date,months) ========================================== Or, without the ATP: =MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1})) will return, for example, 2/29/2008 with 8/31/2007 in A1. --ron |
Adding Months to Dates to create new value
On Fri, 02 Mar 2007 07:23:46 -0500, Ron Rosenfeld
wrote: On Thu, 1 Mar 2007 22:40:15 -0800, simond wrote: I have a column that contains various dates (xx/xx/xxxx) In a column next to this one I would like to add various # months to come up with a new date 8/1/2006 + 6months = 2/1/2007 Is this possible with formula? Thanks You can use the EDATE function. With 8/1/2006 in A1, =EDATE(A1,6) From HELP: Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue. If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. How? On the Tools menu, click Add-Ins. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK. If necessary, follow the instructions in the setup program. Syntax EDATE(start_date,months) ========================================== Or, without the ATP: =MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}) ) will return, for example, 2/29/2008 with 8/31/2007 in A1. --ron I forgot to mention that this formula I first saw posted by Bob Phillips. If you have the following: A1: Date A2: Number of months Then you can try this formula: =MIN(DATE(YEAR(A1),MONTH(A1)+A2*2+1-ROW(INDIRECT(A2&":"&A2+1)),DAY(A1)*{1,0})) --ron |
All times are GMT +1. The time now is 11:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com