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 

0
trebor (88)
4/5/2009 12:35:54 PM
excel.newusers 15348 articles. 2 followers. Follow

8 Replies
534 Views

Similar Articles

[PageSpeed] 40

=DATE(YEAR(I1),MONTH(I1)+1,DAY(I1))

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"KRK" <trebor@yeleek.nospam.freeserve.co.uk> wrote in message 
news:u98STsetJHA.5652@TK2MSFTNGP05.phx.gbl...
> 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 

0
dguillett1 (2487)
4/5/2009 12:48:38 PM
=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" <trebor@yeleek.nospam.freeserve.co.uk> wrote in message 
news:u98STsetJHA.5652@TK2MSFTNGP05.phx.gbl...
> 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 

0
nicolaus (2022)
4/5/2009 12:51:30 PM
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

Regards,
Alan.
"KRK" <trebor@yeleek.nospam.freeserve.co.uk> wrote in message 
news:u98STsetJHA.5652@TK2MSFTNGP05.phx.gbl...
> 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 

0
alan111 (581)
4/5/2009 12:54:24 PM
"KRK" <trebor@yeleek.nospam.freeserve.co.uk> 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))
0
joeu2004 (766)
4/5/2009 4:10:45 PM
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" <joeu2004@hotmail.com> wrote in message 
news:u9zBVkgtJHA.4068@TK2MSFTNGP03.phx.gbl...
> "KRK" <trebor@yeleek.nospam.freeserve.co.uk> 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)) 

0
joeu2004 (766)
4/5/2009 4:18:51 PM
>=MIN(DATE(YEAR(A1),1+MONTH(A1),DAY(A1)),EOMONTH(A1,1))

This will do the same thing:

=EDATE(A1,1)

-- 
Biff
Microsoft Excel MVP


"JoeU2004" <joeu2004@hotmail.com> wrote in message 
news:u9zBVkgtJHA.4068@TK2MSFTNGP03.phx.gbl...
> "KRK" <trebor@yeleek.nospam.freeserve.co.uk> 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)) 


0
biffinpitt (3171)
4/5/2009 5:25:03 PM
"T. Valko" <biffinpitt@comcast.net> wrote:
> >=MIN(DATE(YEAR(A1),1+MONTH(A1),DAY(A1)),EOMONTH(A1,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)) 

0
joeu2004 (766)
4/5/2009 11:36:50 PM
Thanks for the help

KK


"KRK" <trebor@yeleek.nospam.freeserve.co.uk> wrote in message 
news:u98STsetJHA.5652@TK2MSFTNGP05.phx.gbl...
> 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 

0
trebor (88)
4/6/2009 11:42:18 AM
Reply:

Similar Artilces:

date and text concatenation
I tried to concatenate cell A1 with date (27.07.2005) and cell B1 wit text (INVOICE 777) by using formula in cell C1 (=A1&" "&B1)and receive the following result *38560 INVOICE 777*. But I want to see the resul as *27.07.2005 INVOICE 777*. How to avoid the date convertion? Pleas advise. TIA -- littlep ----------------------------------------------------------------------- littleps's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2548 View this thread: http://www.excelforum.com/showthread.php?threadid=39053 =TEXT(A1,"dd.mm.yyyy")&...

Month question
Basically, I would like to create a function where example if I type 02/04/2004, the column next to it will automaticall increment of 2 months, become 02/06/2004. So, my function is below: =DAY(A1) & "/" & (MONTH(A1) + 2) & "/" & YEAR(A1) But, this function got problem, which is the leading zero, meanin 02/04/2004 will become 2/6/2004 How do I fix this? Thanks -- Message posted from http://www.ExcelForum.com =date(year(a1),month(a1)+2,day(a1)) but use format|cells|number tab to give it the format you like. mm/dd/yyyy "ngaisteve1 <" w...

Money 2004 Small Business: Invoice Date vs. Due Date / recurring invoice
I'm trying to set up a recurring invoice to be sent out on a monthly basis. The only way I figure to do this is by entering it into bills and deposits but that wont create an invoice for me automatically. Is there a way? Also, when I create a new invoice, it asks for the date that is pre-filled with todays date. That would be fine if that were the Invoice Date but when viewing the invoice preview, the Due Date is taken from that field also. Therefore, Invoice Date is the same as Due Date. That ain't gonna cut it. I need to bill 2 weeks in advance. Any thoughts? Thanks, BCPower ...

Increase size of pie chart within control?
Is there a way to increase the size of a pie chart within its control? I have a limited amount of space in which to display the chart, so the overall size of the control is restricted. But within it there's quite a lot of "wasted" white space and I would like to make the pie itself larger and more legible. Can do, or not? Many thanks CW In design mode, double-click on the chart within the control to select it for editing. Then use the sizing handles to resize the chart area to the size of the control; this does not have to be square. Click on the plot area to select it ...

I need a formula to sum column b if column a is between two dates
I have an excel spreadsheet with employees time off. I need a formula that will add column b if column a is betwee two dates. For example: if column a is between 9/22/04 and 9/21/05 then add column b. I have tried all different formluas but can't get this to work. ...

DST 2007 patch for CRM 1.2
Hello All, Would anyone know if DST 2007 patch is required for CRM 1.2. Greatly appreciated. We will know when the DST patch is officially released. ============================== John O'Donnell Microsoft CRM MVP http://www.crowecrm.com "Marcos" <Marcos@discussions.microsoft.com> wrote in message news:0CD3253D-5917-4EE3-A09F-6A1484C65C18@microsoft.com... > Hello All, > > Would anyone know if DST 2007 patch is required for CRM 1.2. Greatly > appreciated. I have seen nothing for this on the DST Site and I have 2 clients using v1.2 asking what they s...

How do I copy a date from excel to a outlook calender?
I am looking to produce a spreadsheet with various amounts of data on. One of the bits of data will be appointment dates. Can Excel, (or any other database) update outlook appointments with either little on no user intervention? If so, how? ...

Drivers for percentage increase
I am trying to set up a driver to include in my budget profile so that my managers can select the percentage from a driver and see on each budget line what the outcome will be i.e staff costs may be calculated at 150,000 but if I used my driver to calculate an increase of 3.5% that would change the staff costs to 155,250. Can anyone help? Regards Jo I am not sure what you question is but this may help:- In cell A 2 I have 150,000 In cell B 2 I have 3.5% In cell C 2 I have:- =(A2*B2)+A2 - and this returns 155,250 to cell C 2. If my comments have helped please h...

Year end close with 1 database and 5 entities
I have one data base with 5 entities. Each entity has its own Retained Earnings account. MGP only allows one Retained Earnings account to be used when closing the year. I currently now must do a journal entry to reclass each entities Current Year activity out of that one Retained Earnings account and to its own Retained Earnings account. I have been using 12/31/yy as the date of the reclass entry. It however creates an out of balance for each entity's Balance Sheet. The out of balance amount is the Current Year's activity. So: 1. Does anyone else have this issue? If you...

MS CRM 1.2 and Exchange 5.5?
Does anyone know if it's possible to install the Exchange router on Exchange 5.5? Its not possible - you must have Exchange 2000 or 2003 "Bryan" <anonymous@discussions.microsoft.com> wrote in message news:e16001c3f01d$da3685f0$a401280a@phx.gbl... > Does anyone know if it's possible to install the Exchange > router on Exchange 5.5? ...

Date Range
Hello, I have a table of employee information, which include 2 fields called Start Date and End Date. I want to be able to run a query that enables the user to enter a date range to find those who are between a specific Start Date and End Date. Example: Find those between Sept 1, 2008 (Start Date) and September 30, 2008 (End Date). Thanks, JL You need to be a bit clearer on what you want. I am guessing that you want to match records where the period Start Date to End Date overlaps fully or partially the days in the date range that is input. WHERE FldStartDate <=...

to increase quantity
hello everybody, i have a question: i want to use project to calculate the delivery time of a machine component. I know the bill of material and the time of every step (manufacturing, mounting, etc.). I don't have problem to create a gantt for this item: but i have problem when i want to create a gantt for a number greater than one of my item. I think that i have to use a function that increase the duration of each step of the process, but i don't know how. Please help me. thank you -- sentenza ------------------------------------------------------------------------ s...

Date Add
Hey all, Im trying to do somthing that I am guessing is quite simple. Im working on a Db that tracks a fleet of rental skiis. Every 2 years these skis must be tested. I have a table that tracks all the information about a given pair of skis, including the date they were last tested. What I am trying to do is have a feild within that table autocomplete using the [test date] feild, plus 2 years. This new feild would then become the date when the next test was required. The Table is called [Ski Info] The first date feild is [Test date] the feild I would like to have calculated is [Next te...

Date Wont Display As Date
Greetings, This is a simple one, I hope. Here is the problem formula: ="Service Date"&CHAR(10)&Enter!AI4 I am trying to get this formula to show as: Service Date 01/15/04 Instead I get: Service Date 38001 Any ideas on how to get the date to show and not the date code? TIA -Steve Moulton Minitman, Format/Cell/Date Pick one of your liking. John "Minitman" <excelreply@i-m-p.net> wrote in message news:n40e00hgkdm1a5hlq9neq5echhoqu6htca@4ax.com... > Greetings, > > This is a simple one, I hope. > > Here is the problem formula: > >...

future dates in payables
Hello: An end user accidentally entered payables transactions in future dates such as 2229. We do not want to have to create fiscal periods for years so far in the future. But, if we have to, we will. Anyway, how would we void or get rid of transactions accidentally entered with future periods? Thanks! childofthe1980s Why can't you just delete the transaction? You can't post to a period that hasn't been setup. Frank Hamelly MCP-GP, MCT East Coast Dynamics ww.eastcoast-dynamics.com The client says that there are some transactions in the system like that that she cannot...

Printing 1/2 page postcards
I am trying to print two similarly designed postcards on one page but with different addresses. I am using MS Publisher 2003. Is there any way to do this? thank you Select custom in page setup, type 5.5 in the height box, Publisher will print two per sheet. This is the setup you will use for mail merge and two cards per sheet. If each card is an individual piece, you will have to setup a custom size in your printer. You can use a mail merge or print one card at a time, manually changing the data each time. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ ...

1) lost basis in time for merged stock 2) Bond reocurring payments not available
Has anyone a solution to these important investor problems with MS Money 2002. I downloaded the trial version M2004 and it is the same. Reports show incorret annual rate of return for basis in a merged company: If you record a merger of a stock using the "Record a special activity" feature in an investement account, the dollar amount of your original investment in the acquired company is transferred to the acquiring company, as it should be. When you run an investment performance report, the annual rate of return for your basis is lost. The report displays the result as ...

X axis date
Hi I'm using Excel '97 (Yeah, I know, but it's all I can afford at the moment) I want the X axis to always display a month beyond the latest date, so that the line graph stops short of the right hand side. I can get the Y Axis of value to stay ahead of the game by a consistent amount, but the date defaults back to the latest date when Auto for Max value is ticked. Is this possible? Will I have to use VBA? Please clue me in. Thanks Dave F. Add a series to the chart. It can consist of a single point, where the date is calculated as the maximum in your date column plus 30, an...

Date Increase
I currently have a form for entering current passwords for systems. I have a field to enter the date the password was last changed, and another field for the date the password expires, which is every 30 days. Is there anyway i can set the date password expires field to update automatically? The 2nd field is just calculated from the 1st and better left as a calculation in a query rather than a field in a table. There are no triggers at the table level in Access. Dan Wood wrote: >I currently have a form for entering current passwords for systems. I have a >field to enter...

Differnce between two dates
Hi I want to dermine how mnay days there is between cell c6 and g6 if g6 contains a value, if not then i want thee code to do nothjing. Mnay thanks You didn't mention which is the Start Date and which one is end date (i.e.) whether the start date is C6 Or G6. If the Start Date is C6 and the End Date is G6 then use the below formula =IF(G6="","",DATEDIF(C6,G6,"D")) If the Start Date is G6 and the End Date is C6 then use the below formula =IF(G6="","",DATEDIF(G6,C6,"D")) Remember to Click Yes, if this post h...

can I increase excel column numbers to more than 256?
I am trying to analyse tables including more than 256 variables. I cannot transpose them into rows because the program i am using in association with excel (xlstat) takes the columns as the variables. Is there a way to increase the number of the columns?? or is it a fixed specification of the excel worksheet? thank for any help! Yes, it is fixed - you cannot increase the number of columns. You can, of course, split your data up into chunks of 250 columns - the first block of data on row 1, for example, then the next set starting on another row etc. Pete ...

Graph displaying dates alphabetically on x axis, not by date
Hello! Title says it all - I want the x axis to show dates in DATE ORDER, not alphabetical order. I've read thru the forum, can't see how to fix it - your help will be appreciated. Kind Regards, Russell. p.s. Here is the graph Row Source, should there be an ORDER BY statement in here? SELECT (Format([Date Paid],"mmm"" '""yy")) AS Expr1, Sum([Tenant Payments]. Amount) AS SumOfAmount FROM [Tenant Payments] GROUP BY (Format([Date Paid], "mmm"" '""yy")), [Tenant Payments].[Date Paid], (Year([Date Paid]...

Dates
I am working on a spreadsheet that consists of a rolling 12 mont period. Once a month has passed how do I delete it and add a new mont to the 12 month period? Please help -- ~~ Message posted from http://www.ExcelForum.com You have to provide more info on how your data is laid out. Tell us what you have and tell us what you want to have when the month changes. HTH Otto "erodri02" <erodri02.y6nsy@excelforum-nospam.com> wrote in message news:erodri02.y6nsy@excelforum-nospam.com... > > I am working on a spreadsheet that consists of a rolling 12 month > period. Once...

Date filled down?
I would like to put a date in a cell. Then fill it down. How to do that? Like.... Tuesday, July 06, 2004 Wednesday, July 07, 2004 ..................... Hi Ming type the date as dd/mm/yy or mm/dd/yy or whatever is default for your region, then choose format / cells click on custom, type in the box on the right had side dddd, mmmm dd, yyyy click ok now click on fill handle (bottom right of cell) and fill down as far as you want - adjust column width if needed. Cheers JulieD "Ming He" <hepub@hotmail.com> wrote in message news:e$4TSb9YEHA.3304@TK2MSFTNGP09....

$997 PROFIT Per Order 1 to 2 Deals Per Day! #2
$50,000 a month That's What Our Top Associates Earned Last Month Without Ever Talking To A Prospect. www.LiveTrustedPartners.com ...