 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2007Series of dates, increment 1 month
Given a date in a cell named CurrentDate, I'd like to generate a series of dates, each 1 month later than the previous date.. If I use =DATE(YEAR(CurrentDate), MONTH(CurrentDate)+ROW()-2, DAY(CurrentDate)) And the start date is, say, 31 Oct 2007, then the dates get messed up for months with less than 31 daze. I was wondering whether any Excel date functions handle month increments better than the above. Otherwise, I'll write a VBA function to do this. Howard Is this what you want? =IF(B1=EOMONTH(B1,0),EOMONTH(B1,1),B1+MONTH(B1)+1) Regards Peter > Given a date in a cell named CurrentDate, I'd like to generate a series of > dates, each 1 month later than the previous date.. [quoted text clipped - 9 lines] > better than the above. > Otherwise, I'll write a VBA function to do this. The question is.... what date do **you** think is one month from 31 Oct 2007 is? Same question with 28 Feb 2007? Rick > Given a date in a cell named CurrentDate, I'd like to generate a series of > dates, each 1 month later than the previous date.. [quoted text clipped - 9 lines] > better than the above. > Otherwise, I'll write a VBA function to do this. >Given a date in a cell named CurrentDate, I'd like to generate a series of >dates, each 1 month later than the previous date.. [quoted text clipped - 9 lines] >better than the above. >Otherwise, I'll write a VBA function to do this. It depends on what you want for an answer. Frequently, people want the series to not "spill over" into the succeeding month. For example: 1/31/2007 2/28/2007 3/31/2007 4/30/2007 5/31/2007 6/30/2007 7/31/2007 Of course, if you start with 2/28, then you have: 2/28/2007 3/28/2007 4/28/2007 5/28/2007 6/28/2007 7/28/2007 8/28/2007 If that is acceptable, then try this formula: =MIN(DATE(YEAR(CurrentDate),MONTH(CurrentDate)+ROWS($1:1)+{1,0},DAY(CurrentDate)*{0,1})) and fill down as far as needed. If you want a different kind of adjustment, you'll need to specify. --ron Hi I had another look at it =IF(A2=EOMONTH(A2,0),EOMONTH(A2,1),IF(A2<EOMONTH(A2,0),DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)))) produces the following list: 31/10/2007 01/11/2007 02/11/2007 30/11/2007 01/12/2007 02/12/2007 31/12/2007 01/01/2008 02/01/2008 31/01/2008 01/02/2008 02/02/2008 Is this OK? Peter > Given a date in a cell named CurrentDate, I'd like to generate a series of > dates, each 1 month later than the previous date.. [quoted text clipped - 9 lines] > better than the above. > Otherwise, I'll write a VBA function to do this. Hello Billy, ...but if A2 is, for instance, 30th Jan 2007 then the next date produced by that formula will be 2nd March 2007....which I presume is not the required answer. If using Analysis ToolPak functions is acceptable then, with first date in A2 use this formula in A3 copied down =EDATE(A$2,ROWS(A$3:A3)) > Hi > I had another look at it [quoted text clipped - 24 lines] > > better than the above. > > Otherwise, I'll write a VBA function to do this. I have not yet had a chance to look at th esuggested formulae. Currently, I'm using =nextmonth(CurrentDate, ROW(), ROW(CurrentDate)) Along with: Option Explicit Public Function NextMonth(StartDate As String, RowNum As Long, StartRow As Long) As String Application.Volatile True NextMonth = Format(DateAdd("m", RowNum - StartRow, StartDate), "d mmm yyyy") End Function
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.