|
| 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
April 5th, 2009, 01:35 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
Increase a date by 1 month
Hello,
I have a date in in one cell, eg 22/04/08 and I want the next cells to be one month higher, ie 22/05/08 , 22/06/08 etc etc. The number of days in a month varies so I cant just add 30. Can anyone help & advise please Thanks KK 
|
|
#2
April 5th, 2009, 01:48 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Increase a date by 1 month
=DATE(YEAR(I1),MONTH(I1)+1,DAY(I1))
-- Don Guillett Microsoft MVP Excel SalesAid Software "KRK" wrote in message ... Hello, I have a date in in one cell, eg 22/04/08 and I want the next cells to be one month higher, ie 22/05/08 , 22/06/08 etc etc. The number of days in a month varies so I cant just add 30. Can anyone help & advise please Thanks KK
|
|
#3
April 5th, 2009, 01:51 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Increase a date by 1 month
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
But do think of what you want the result to be in cases like Jan 31. -- Kind regards, Niek Otten Microsoft MVP - Excel "KRK" wrote in message ... Hello, I have a date in in one cell, eg 22/04/08 and I want the next cells to be one month higher, ie 22/05/08 , 22/06/08 etc etc. The number of days in a month varies so I cant just add 30. Can anyone help & advise please Thanks KK
|
|
#4
April 5th, 2009, 01:54 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Increase a date by 1 month
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
Regards, Alan. "KRK" wrote in message ... Hello, I have a date in in one cell, eg 22/04/08 and I want the next cells to be one month higher, ie 22/05/08 , 22/06/08 etc etc. The number of days in a month varies so I cant just add 30. Can anyone help & advise please Thanks KK
|
|
#5
April 5th, 2009, 05:10 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Increase a date by 1 month
"KRK" wrote:
I have a date in in one cell, eg 22/04/08 and I want the next cells to be one month higher, ie 22/05/08 , 22/06/08 etc etc. The number of days in a month varies so I cant just add 30. Can anyone help & advise please =MIN(DATE(YEAR(A1),1+MONTH(A1),DAY(A1)),EOMONTH(A1 ,1)) If you do not want to rely on the Analysis ToolPak (for EOMONTH), then: =MIN(DATE(YEAR(A1),1+MONTH(A1),DAY(A1)), DATE(YEAR(A1),2+MONTH(A1),0))
|
|
#6
April 5th, 2009, 05:18 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Increase a date by 1 month
PS....
I forgot to mention that you might need to explicitly select the Date format. Also, if you enter two such dates a month apart, you can select the two cells and drag them down (or across if the two cells are in a row), and Excel will effectively do this computation automagically. However, what you get are constants, not formulas. So if you change the first two dates later, you will have to repeat the drag operation, although double-clicking on the drag handle might work for you then. ----- original message ----- "JoeU2004" wrote in message ... "KRK" wrote: I have a date in in one cell, eg 22/04/08 and I want the next cells to be one month higher, ie 22/05/08 , 22/06/08 etc etc. The number of days in a month varies so I cant just add 30. Can anyone help & advise please =MIN(DATE(YEAR(A1),1+MONTH(A1),DAY(A1)),EOMONTH(A1 ,1)) If you do not want to rely on the Analysis ToolPak (for EOMONTH), then: =MIN(DATE(YEAR(A1),1+MONTH(A1),DAY(A1)), DATE(YEAR(A1),2+MONTH(A1),0))
|
|
#7
April 5th, 2009, 06:25 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Increase a date by 1 month
=MIN(DATE(YEAR(A1),1+MONTH(A1),DAY(A1)),EOMONTH(A 1,1))
This will do the same thing: =EDATE(A1,1) -- Biff Microsoft Excel MVP "JoeU2004" wrote in message ... "KRK" wrote: I have a date in in one cell, eg 22/04/08 and I want the next cells to be one month higher, ie 22/05/08 , 22/06/08 etc etc. The number of days in a month varies so I cant just add 30. Can anyone help & advise please =MIN(DATE(YEAR(A1),1+MONTH(A1),DAY(A1)),EOMONTH(A1 ,1)) If you do not want to rely on the Analysis ToolPak (for EOMONTH), then: =MIN(DATE(YEAR(A1),1+MONTH(A1),DAY(A1)), DATE(YEAR(A1),2+MONTH(A1),0))
|
|
#8
April 6th, 2009, 12:36 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Increase a date by 1 month
"T. Valko" wrote:
=MIN(DATE(YEAR(A1),1+MONTH(A1),DAY(A1)),EOMONTH(A 1,1)) This will do the same thing: =EDATE(A1,1) Well, duh! But I get paid by the keystroke :-). Seriously, I think we both made the same mistake. Consider that A1 is 1/31/2008, and we put =EDATE(A1,1) into A2 and copy down. A2 will be 2/29/2008 (good), and A3 and all subsequent dates will be m/29/2008, until after 1/2009, when all subsequent dates will be m/28/2yyy (oops!). I think the correct formula starting in A2 and copied down is: =EDATE($A$1,ROW(1:1))
|
|
#9
April 6th, 2009, 12:42 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Increase a date by 1 month- Thanks
Thanks for the help
KK "KRK" wrote in message ... Hello, I have a date in in one cell, eg 22/04/08 and I want the next cells to be one month higher, ie 22/05/08 , 22/06/08 etc etc. The number of days in a month varies so I cant just add 30. Can anyone help & advise please Thanks KK
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
