Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding 6 Months to a Date | Excel Worksheet Functions | |||
Adding months | Excel Worksheet Functions | |||
Adding 6 months to any given date | New Users to Excel | |||
Adding months to dates should account for 28-30-31 day months | Excel Worksheet Functions | |||
Adding months. | Excel Discussion (Misc queries) |