Free Microsoft Excel 2013 Quick Reference

increase one month

Hello All,

Is there any formula which can increase the above cell by one month.
For example,

The sheet looks like this,

January
=formula -------------> February
=formula--------------> March
..
..
..
=formula---------------> December

Thanks


Hi all,

I am hoping for help with two date formula queries I have.

I have built a spreadsheet which consultants at my work (a recruitment agency) list their successful job placements on. We record the placements monthly, and each month I simply copy and paste the previous month's 'template' (a 'block' of 60 rows) down into the area immediately below. The 'template' then has all the right formulae in the right place, one of which is a formula to auto increase the month by one. So if the previous month was March 2009, the next pasted 'block/template' will automatically be entitled April 2009. The placements are then summed up automatically per consultant per month with further formulae in the template that make calculations based on the data in the month's 'block'.

Some recent changes to the info we want to see per consultant per month leads me into my 1st question:
Instead of just listing March 2009, I now need (I think, unless Excel can be a bit clever) the date to be essentially the range of the whole month, ie 01/03/2009-31/03/2009 (I am in the UK), or for months with fewer days to cover the relevant period ie 01/02/2009-28/02/2009. Is there a way to do this? And let me explain why I think I need it in my 2nd question (the resolution to question 2 may mean my question 1 is not the best way of going about things).

I have a formula that totals the placement fees per consultant by using a sumproduct. The formula (in cell AN154) basically adds up a fee if the consultant's initials by the fee match the consultant's initials where I want the calculation to take place (cell A135). I now need to have a further if criteria, and the formula is getting a bit complicated, so I would appreciate some advice. The formula is as follows:

=SUMPRODUCT((I136:I189=AD154)*(K136:K189))

where the consultant's initials for the calculation are in cell AD154 and column I is where a consultant puts their initials in the data section. Column K is the placement fee.
I now need to modify the formula so that it checks the date an invoice will be raised for a placement for a certain month (ie I need to know the total value of invoices that will be raised in February for a specified consultant - placements are listed when they are made but we only invoice on the start date of a candidate and due to the nature of jobs a placement could be confirmed one month but the candidate may not start till the next month or even the month after).

So this is why I asked the first question. My modified calculation formula needs to check if the placement will be invoiced in the month range of the 'block' which will be copied & pasted down for the next month and so on. So it needs to sum any fee which matches the relevant initials, if the invoice date is with the date range of the month block that this formula is in. (eg if we are looking at the block for March 2009, for placements made by the consultant with initials MG, the calculaton needs to check the entire spreadsheet for fees associated with MG that will be invoiced in March 2009).

Column S is where consultants list the date for their placement to be invoiced. Column I is where they list their initials. Column K is where they list the fee value. My modified formula will have to look at the total columns rather than just the 60 rows in each 'block' because, as mentioned earlier, placements made one month can be invoiced in later months. Can someone help me with the modification of the formula? And also how to create the self calculating month formula which heads up each 'block' (I currently use

=DATE(YEAR(A67), MONTH(A67)+1, DAY(A67)) where A67 is the previous month in the previous 'block' in the format 01/03/2009.

If it can't be done in one cell, I am happy to use my existing formula on two separate cells as long as the calculation formula can check a date range between a date in one cell and a date in another cell - and as long as the different number of days in each month can be handled by excel somehow? Ie having 01/01/2009 in cell A1 and 31/01/2009 in cell B1, using my above formula would produce 01/02/2009 in cell A2 but 03/03/2009 in B2 - I need it to be 28/02/2009.

sorry for the length of my post, it's hard to explain concisely!

In fact, I have just realised I can upload my file. Cell A135 is where I would like to enter the date range by formula.

Cell AN154 is where I would like to modify my formula to check if the invoice date (ie check the whole of column S) is within the month of cell A135.

That way I can simply copy the block for a new month and the whole spreadsheet is self calculating, apart from cell A67 which is where I need to enter the starting point for the dates manually.

Can anyone tell me what formula I need to put in AN154 and how to do the auto month increase problem? Thanks in advance.

Hi all,

I have an array of values and dates. I would like to use a vlookup to find the corresponding value for a date one/two/three month onwards from the original. For example, for user BBJ and date 31/7/2005, I would like the value of this user on this date and 31/8/2005, 30/9/2005 and 31/10/2005 as well.

My data is set-up as follows: in sheet 1 is the raw data, column B is the name of the user, column C is the corresponding value, and column D is the date (all month-ends). The data is sorted alphabetically by the names in column B and then the date in column D in oldest to newest.

In sheet 2, what I have at the moment is a list of dates in column A, the names of the users in column B. I would like to use a vlookup or some other Excel function/macro that can tell me the value of that user on the date in column A (time=t) put the value in column C, and also the value of that user one month on (time =t+1 month) in column D, value of user two months on (t+2 months) in column E and also the value of the user three months on (t+3 months) in column F.

How should I go about doing this as each month has different number of days, so I can’t really just add +30 to the date in column A to use in the vlookup (if that is indeed the function that I need to use to sort out this mess of my raw data dump).

Thanks in advance for all help received.

Hi - I'm trying to populate a combobox with a date that is one month prior to today (the day of the month is not important). I have a table with dates in from Jan 2004 to, well, later.

Our reports are generated on monthly data one month in arrears so if today is 7th of September my start month will be Jul-06 and my end month is Aug-06. Code is structured to take into account a current date in Jan or Feb with a corresponding start month in Nov and Dec of the previous year.

I want to prepopulate the comboboxes, however they remain obstinately at Jan-04 no matter what I do.

Here's what I've done so far (ps: I don't know which event is best to link this to - I've tried form load and that didn't (appear to) work, so suggestions on that appreciated too).


	VB:
	
 Single) 
     
    Dim EndDateMonth As String 
    Dim EndDateYear As String 
     
    EndDateMonth = Month(Now()) 
    EndDateYear = Year(Now()) 
     
    If Month(Date) = 1 Then 
         
        EndDateMonth = 12 
         
        cboEndMonth.DefaultValue = "01/'" & EndDateMonth & "'/'" & EndDateYear - 1 
        cboStartMonth.DefaultValue = "01/'" & EndDateMonth - 1 & "'/'" & EndDateYear - 1 
         
    ElseIf Month(Date) = 2 Then 
         
        EndDateYear = Year(Date) 
        EndDateMonth = 13 
         
        cboEndMonth.DefaultValue = "01/'" & EndDateMonth - 12 & "'/'" & EndDateYear 
        cboStartMonth.DefaultValue = "01/'" & EndDateMonth - 1 & "'/'" & EndDateYear - 1 
         
Else: 
         
        cboEndMonth.DefaultValue = "01/'" & EndDateMonth - 1 & "/" & EndDateYear '
        cboStartMonth.DefaultValue = "01/'" & EndDateMonth - 2 & "/" & EndDateYear 
         
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks,

Can some one please help me find a way to take a date in a previous row and
get to the same date one month ago. I do not know how to account for the 30
or 31 day discrepancy. Also, if possible could it be the last business day
one month ago. Thank you very much

I'm looking for a formula that displays the billing-month start date based on
a user-entered end date. The system I will be using this calculation for
considers billing month periods as ending one month less one day after the
billing month start date. For example, if my billing month starts on April
1, it ends on April 30. If starts on May 1, it ends May 31. If starts on
April 15, it ends on May 14. If starts on February 28, it ends on March 27
(non-leap year). If starts on February 29 (leap year), it ends on March 28.

For example, if a user enters an end date of 31-JAN-08, the billing month
start date that the formula will calculate should show 01-JAN-08. If the
user enters an end date of 15-JAN-08, the billing month start date the
formula will calculate should show 16-DEC-07.

I thought a very easy way to do this would be using the DATE function:
=DATE(YEAR(H5),MONTH(H5)-1,DAY(H5)), where H5 is the cell the user enters the
end date into.

Below are the results I achieved using this formula, which are all over the
place. I have included here only months that have 31 days in them to show
how this formula yields different results even on months with the same number
of days. The first column lists the user-entered end date I entered into
Excel, and the second columns shows the results given by the DATE formula I
listed above.

User-entered end date Billing-month start date
Jan-31-2008 Dec-31-2007
Mar-31-2008 Mar-02-2008
May-31-2008 May-01-2008
Jul-31-2008 Jul-01-2008
Aug-31-2008 Jul-31-2008
Oct-31-2008 Oct-01-2008
Dec-31-2008 Dec-01-2008

Only the months of May, July, October, and December yielded the correct
billing-month start date (which starts on the first of the month in this
example).

The results using this formula were equally chaotic for months with only 30
days in them.

Once last thing to note: In my sample dates above I used the last day of
the month as the user entered end date in order to give a consistent example.
In practice, the user-entered end date can be any day of the month, not just
the last day of the month.

01-Dec-10 56 Monthly Premium 58 01-Feb-11 112 Monthly Premium 58 01-Feb-11 448 ADJ 58 01-Mar-11 448 ADJ 58 01-Mar-11 -896 COR 58 01-Mar-11 56 Monthly Premium 58 01-Apr-11 58 Monthly Premium 58 01-Jun-11 122 Monthly Premium 58
Is there a formula that will do the following:
1. Check whether there is an entry "ADJ" in the one month (Column
C) and an entry "COR" and/or "ADJ" (Column C) in the next month and
if so,
2. The formula must check whether the sum of the ADJ entries (Column
B) + Sum of COR entries = 0.
3. If 1 and 2 is false, the entries must stay as it is.

PS. This is just an extract from my worksheet. The dates in column A are ongoing, thus it must constantly compare for example, Jan and feb, feb and march, march and april etc.

I hope this is not to confusing.

Is there a way to add a month to a series of data that is not contiguous? Example:

Row 1 June 1, 2006
Row 5
Row 9
Row 13

I want a formula in rows 5, 9 and 13 that will add one month to the value in Row 1 so that if I change that value the others will change as well.

Thanks

Hi all

I have a log which I am creating, and I need to write a macro/formula to automatically fill in some dates. When I input a date in to Column D, I want Excel to fill in the date two weeks after that in Column K and one month after it in Column L. This is so that I can keep track of when things are due. I have seen it done before on an old spreadsheet I used but I dont have a copy any more... Hope someone can help!

Thanks

How can I skip one month by one month
A1 01/01/2005
A2 28/02/2005
A3 31/03/2005
how can I scroll to the A12 with 31/12/2005?

Sir,
The requirement is an email should get generate or send automatically using outlook to the given mail id in coloum K before one month of the contract end date ( Coloum J). Also a mail should send automatically to the mentioned address (coloum M) if we fill a date in the coloum of Maintanance date(K coloum). The procedure for sending mail should be auto and not need to open the excel sheet. The mail should send as soon as the date reaches.

1) The message in mail should be Sir, Your AMC contract of machine model ----( coloum G) Sr.No.------(coloum H) is going to end on -----( take date from the coloum J). Please contact us for renewal of the contract.

2)The second mail should send if we enter a date in the coloum M. MESSAGE:- The preventive mailtanance of machine model ----------( coloum G) Sr. No.---------(coloum H) has been completed on ------( Coloum M)

Regards,
Shri

My apologies in advance if part of this question is off topic.

How do I use vba to increment a date by one month ....

and (probably off topic)

What is the usual practise for dating 'monthly' invoices when the date of
previous invoice falls in the range 29th to 31st and the following month has
less days

Regards & TIA

I am setting up an amortization table and want to add one month to the
previous month:

Ex: b9 has the start date 12/1/04
b15 needs to be the next month Jan 05
b16 Feb 05 and so on for 10 years

The problem comes in 2007 (must be a leap year) it skips a month and
throughs the whole thing off.

I am trying to add a formula in Excel to show a percent increase or decrease in sales from one month to the next and I can't figure out the formula...Columns are: month and total sales(in dollars). I need to compare month 2 to month 1 and show how much the sales increase or decreased on a percentage basis (Ex: January $10,000 and February $15,000...the answer I want is a positive 50%.)

Thanks.

I am trying to figure a formula that will give me a monthly increase with a annual target. Example:

2006 sales were $100,000 and December 2006 sales were $9,000.

For 2007 I would like a 10% increase in sales which means I should end up with $110,000 at the end of 2007.

The formula should figure a gradual sales increase each month until December 2007. I am assuming the business sales trend is upward with no seasonality so that January 2007 should be the annualized increase over December 2006.
.
I have used the formula =100000*((1+.1)^(1/12)-1) which works for 2007, but it gives me a large jump between December 2006 and January 2007 and then a slight increases each month.

I am lost how to do this some any suggestion would be appreciated.

Hi, I am in the real estate business and need to create a formula based on the following logic/scenario:

We typically give out Free Rents (usually a few months) to our tenants, which is always up on the front. And then we would charge a rent increase ($.50 per year in the example) starting 12 month after the rent free rent ends and every year (12 months) afterwards. The rent will be flat between each increase.

My questions have two parts:

1. Is this something array formula can handle in one formula?

2. Since I am not an expert on array formula, what I ended up doing was to create a line for "Rent Bump Date" which is defined as the Free Rent + 13 for the 1st rent increase (meaning if you dont get free rent you would start paying increase in month 13 and every 12 months after. And I put in what the rent would be for each rent bump date. My solution is if the rent for each month (1 - 240) would be determined based on the ranges of those bumps.

For example,

Month 1-15 Rent $20
16-27 20.50
28-39 21
40-51 21.5
52-63 22
64-75 23
......
......
......
232-243 30

Please note that both and month and rent are not fixed but logical values.

I guess this would be a typical multiple IFs if not for its limitation (I need 20 bumps which would be 20 arguments). I was told I may be able to do this with Name Formula or Concatenate. And I have tried the Concatenate which looks like would be a MONSTER formula with 20 OR statements in it.

Is there a way the array formula can be applied here as well? I have searched this website and found a thread containing a similar situation where a LOOKUP was used beatifully. The difference is my ranges and results are all logical values instead of fixed values.

I am looking for answers to both of my questions. If anyone can shred any light on it, that would be great. I attached the sample here. Ask if you are not clear.

Thanks a million!!!!

I have no idea how to do this, but am sure there are people a lot smarter than me can figure it out.

If I want to have say a 10% annual growth at the end of the year compared to previous yera, how do I figure the monthly increase to end with with each month growing, but year end equal to 10% growth.

Ex: 2006 Sales were $100,000. I want to have a 10% increase in sales from 2006 to 2007 so at the end of 2007 my sales should be $110,000. How do I determine how much of an increase each month will give me that figure.

Thanks

Hi,

I have a column showing profit over one year and i would like to
calculate the percentage increase each month.

For example my columns might be as follows.

Jan - £3000
Feb - £4500
Mar - £7000
Apr - £9000
etc.

So i need to calculate the percentage increase from Jan to Feb and so
on.

Does anybody know the formula?

Thanks

--
fibregrid
------------------------------------------------------------------------
fibregrid's Profile: http://www.excelforum.com/member.php...o&userid=25707
View this thread: http://www.excelforum.com/showthread...hreadid=401911

I have created a Pivot Table in the enclosed file. However the data is not displaying exactly how I would like.
1/ I only want one monthly total for each 'letter' (Column A) rather than multiple July & August entries.
2/ As I add 'data' each week, how do I avoid having to recreate a new Pivot table to reflect the 'latest' results.
Any help is appreciated. K. (Using Excel 2002 SP3)

I submitted this question in another thread and had some excellent help. The question was if it is possible to increase one cell by 1 when the date was changed in another cell. I installed the code that was given to me and it worked perfectly, thanks agin JB and royuk, UNTIL the cell that was to increase by 1 was locked and the sheet protected. The date cell is not locked.

Hi all,

i have a simple question. i would like to increase the month by one. i know that there is the "dateadd" function. however, coding error "type mismatch" occurs when i tried to run the macro.

this is what i had done:
dim s as string
s=format(date,"mmmyy")
range("b1").value = s
range("b2").value = dateadd("m",1,s)
current month : sep10
next month : oct10 (results from using macro)

i am pretty sure that the coding for this is very straightforward but i cant seem to find any solution online. any help would be greatly appreciated

I am trying to figure a formula that will give me a monthly increase with a annual target. Example:

2006 sales were $100,000 and December 2006 sales were $9,000.

For 2007 I would like a 10% increase in sales which means I should end up with $110,000 at the end of 2007.

The formula should figure a gradual sales increase each month until December 2007. I am assuming the business sales trend is upward with no seasonality so that January 2007 should be the annualized increase over December 2006.
. I have used the formula

=100000*((1+.1)^(1/12)-1) which works for 2007, but it gives me a large jump between December 2006 and January 2007 and then slight increases each month.

I am lost how to do this some any suggestion would be appreciated.

Thanks

I need a function which is like vlookup in the fact that when it reads a date
in July, it then goes and finds the July cell in a range and then goes x
number of columns across to find the number. Vlookup does this only for
specific dates but I need a more generalised version which recognises that
the date is part of one of the dates in the month.
Can anyone help?

Hi,

I have a column showing profit over one year and i would like to calculate the percentage increase each month.

For example my columns might be as follows.

Jan - £3000
Feb - £4500
Mar - £7000
Apr - £9000
etc.

So i need to calculate the percentage increase from Jan to Feb and so on.

Does anybody know the formula?

Thanks

Hi,

I intend to use a plugin for WordPress that takes data from Excel and inserts it into WP to create posts. The columns include the post, the post title and a timestamp (date)

I have at least a year's worth of posts :-) which I want to upload

I need to be able to take a starting date and go backwards for a year, simply to save me manually typing out each date.

The format of the starting date is Mar 19, 2008 16:54:11 PM

The minutes don't matter - they're just there to make the plugin work

What I want to do though is go back through the months, with each month being accurate i.e. if there are 30 days in one month, and 31 in another, that's got to be accurate.

If I try and do this manually it's going to be mind-numbing.

I've followed the instructions in Excel 2000 without any success.

Create linear and growth trends with the Series command
When you use the Series command, the resulting series replaces the original selected values. To save the original values, copy them to a different row or column. Then create the series by selecting the copied values.

Select the cell where you want to start the series. The cell must contain the first value in the series.

On the Edit menu, point to Fill, and then click Series.

Do one of the following:
To fill the series down the page, click Columns.

To fill the series across the page, click Rows.

In the Step value box, enter the value that you want to increase the series by. In a linear series, the step value is added to the first starting value and then added to each subsequent value. In a growth series, the first starting value is multiplied by the step value. The resulting product and each subsequent product is then multiplied by the step value.

Under Type, click Linear or Growth.

In the Stop value box, enter the value you want to stop the series at.
Note If there is more than one starting value in the series and you want Microsoft Excel to generate the trend, select the Trend box. In a linear series, the starting values are applied to the least-squares algorithm (y=mx+b) to generate the series. In a growth series, the starting values are applied to the exponential curve algorithm (y=b*m^x) to generate the series. In either case, the step value is ignored. The series created is equivalent to the values returned by the TREND function or the GROWTH function. I've also tried creating a starting date, and then a date one day less above it, and dragging the corner handle up the page, but all I get is a list of identical dates, even when I right click on this and try and see if there are any options.

Any help would be greatly appreciated.