# Previous Month Formula

Feb 19, 2008I have a month number in H2 (1-12). I want a formula that will give me the previous month number. So, if h2 = 1. I need my formula cell to equal 12.

I have a month number in H2 (1-12). I want a formula that will give me the previous month number. So, if h2 = 1. I need my formula cell to equal 12.

ADVERTISEMENT

I am using the following formula to show the first day of previous 6 month cycle.

=DATE(YEAR(TODAY()),MONTH(TODAY())-6,1)

That formula would display: 07/01/2009

I want to have another formula show the last day of the previous month.

Basically it would display: 12/31/2009

I have an issue whereby I have thousands of lines of data. I need to bring back the "previous month" worth of data (along with some additional information) but not sure how to go about this.

The attached shows the example file. I need the following :

1. Take the data to populate the "Populate" tab

2. Using Column F (Invoice Date Created) to only bring back the "previous month data" - in this case it would be May 2014

3. The following formula is what I have currently for Column B of the "Populate" tab: =SUMPRODUCT(SUMIFS('Data'!$N:$N, 'Data'!$M:$M, "Invoice Payment Processed", 'Data'!$A:$A, A2))

4. The above formula brings back all the values - I only want the previous month.

5. I am then not sure how to calculate Column C - which is a count of the amount of invoices that make up the total

I have an excel document which I am trying to automate using VBA. I have two headings which have the same text every month but the month value must change.

Basically I’m looking for the last day of the previous month. So if it was 05th April 2007 I want to return 28 March 2007. I just need to obtain the last day of the previous month in the following format ’28 March 2007’.

Need to be able to highligh data within a range where the date in a column is for the previous month/s- therefore need to be able to also include in macro entering the current month - don't want to have to edit macro each month

View 2 Replies View RelatedI have a macro that saves a workbook with the month name automatically generated in the filename with the code below (apologies for the scrappy code, but I am rather new to VBA)

ActiveWorkbook.SaveAs Filename:= _

"I: EngineeringCapacity PlanningCapacity Report CONTROLS " & Format( Date, "mmmm") & ".xls" _

, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _

ReadOnlyRecommended:=False, CreateBackup:=False

In another file I want to re-open this workbook. However, the problem arises because this will not happen till the next month, in order to update the new month's workbook with the old information. Currently I am 'cheating' by modifying the code with the actual month name, like this

Workbooks.Open Filename:="I:EngineeringCapacity PlanningCapacity Report CONTROLS April.xls" I have tried to use the Dateadd function (with a -1 as the add amount) but I have failed to make it work.

what formula should I use to refer to the last working day on the prioir month. I have trie to look up forums but no result os yet.

View 4 Replies View RelatedI m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.

Details:

Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.

- if under $25K, recognize in next month (month N+ 1)

- $25K-100K, recognize in two equal parts in months N + 1 and N + 2

- over $100K, recognize in three equal parts over 3 months

N + 1, N + 2, N + 3 ...

I have to do a calculation that uses the number of days of the previous month. Thought this would work but it doesn't.

[Code].....

I have a cell in which I will input the last day of a month (e.g., 1/31/09, 2/28/09, etc.) In another cell, I want Excel to show the last day of the previous month. Is there a formula to calculate this automatically?

I enter in Cell 1: 1/31/09

Excel calculates in Cell 2: 12/28/08

I enter in Cell 1: 2/28/09

Excel calculates in Cell 2: 1/31/09

I'd like to make a template so that when it is opened the previous month name is inserted in the sheet name. The reason I want to use previous month is that normally this report is created in the month following the month being reported. So it is opened, months are updated, then the user saves as xls. Ideally I guess sheet would not have the month on the template.

The name of the sheets are shortened months like "Dec Results Bob" and there are 8 of these.

I can tell it might start:

Private Sub Workbook_Open() (or would .xlt be the same?)

but that's about it. I made a macro of renaming sheet but that didn't really tell me if I could insert the Month there.

Attached is an example of what I'm looking for. Example: Column A has all the month names with a defined named range of crnt. D6 has a data validation display with a value of Nov 09, I need E6 to display Oct 09 automatically from the data in Column A. Basically I need cell E6 to always display the previous months text based off cell D6. I think the example sheet explains it better.

View 2 Replies View RelatedHow to write a VBA code so that it would calculate the date of the previous Friday and from there Return the date exactly 1 month before; on any given day.

For example...

today is may 8th

So if I run the code the dates returned for last friday should be 05/02/2014

and 04/04/2014 should be returned AS the DATE exactly a month from 05/02/2014

Trying to word this right. I have one cell with a date of 01/01/2010. I have other cells that I want to be equal to this cell plus 1 or more months.

For example A1=01/01/2010

I want A2 to = 02/01/2010 based on one calendar month entered into A1. So if A1 changes 03/01/2010, A2 will = 04/01/2010.

Is there a function to put in a new column to get the abbreviated year and month of a previous column in the same row???

Ex. Row A B C D

3/14/2007 5/16/2007 2007-3 2007-5

Instead of using the YEAR MONTH funcion for each row is there a universal function that I could designate to the whole column of C and D something like C=YEAR(A(row#))???

This is for a report and on "Summary Worksheet" I want to post "Current Payment" totals IF the invoices from "Tab 3" equal the "month" in G6. Say the report is for January - if there are invoices on Tab 3 -worksheet with a January date I want to post all invoice amounts on Summary worksheet under current payment.

View 4 Replies View RelatedI have log data in two columns:

Column A: Date/time (at 30 minute intervals)

Column B: Numeric data

On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C.

The end of the range is determined by the month in the current row.

I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.

I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach.

Manual

=IF(OR(MONTH(A1009)=A4)*(A$4:A$65536

I have a spreadsheet which I use to track when a work request is recieved, when we confirm the request and when we action the request. I have been trying to write some code to count the amount of requests, receipts and actions we have processed in the last month.

My first column shows who the request is from

The second shows date recieved

The third shows date we send receipt

The fourth shows the date actioned.

I'm sent a file every day where column A contains a month & year but in "general" format.

For example;

Column A, starting with cell A1 with the next date in A2, etc

Jan15

Feb15

Mar15

Apr15

Is there a way through a formula or macro that I can use in order to run through all the dates in this column to make sure that the next cell down is always the month after the cell above it? The dates go all the way through to 2018 in the above format.

I am trying to create a formula that compares month over month data. If the prior month is 0 I get an error. I am having trouble with incorporating ISERR into the formula to eliminate the error.

=IF((C26-B26)/B26

see attached file. Need to find latest non blank value - in attached file it is highlighted in yellow. From there, want to summarise 26 weeks back so, in the attached file:

Row 2 would be finding 750 and summarised back 26 weeks from 30 sep 2012

Row 3 would be finding 2250 and summarised back 26 weeks from 2 dec 2012

Row 4 would be finding 5000 and summarised back 26 weeks from 4 nov 2012

Yesterday (13/03/2007) was the 9th working day of the month (basing a week on Mon – Fri) – today is the 10th working day

Is there any formula, or mix of formula’s I can use to automatically calculate this?

I have a sheet that shows employee absences, I require a formula that will look to see if a friday and a monday are populated and auto-populate the saturday and sunday to match the run, if the only the monday is populated then the saturday and sunday need to stay blank, if only the friday is populated then I also need the saturday and sunday to stay blank.

I have attached a sheet with the current display and the desired outcome.

fill test.xlsxâ€Ž

I have a column of numeric values in column B. I have the date that corresponds with each value in column A. I am using a max formula to find the highest value in column B. What could I use to return the date of the highest value from Column A?

View 2 Replies View RelatedAfter a formula to copy the contents of a cell in the previous sheet in a workbook, so that if I was to copy the last sheet in a workbook the formula would automatically reference the cell from the copied worksheet and so on if I copied tht one.

Hope this makes sense I have a lot of formulas referencing the previous sheet and everytime I copy this sheet to create a new sheet I have to change the sheet number in the formulas.

eg, in sheet 8 this formula get info from sheet 7 cel J30 ='7'!J30 when i copy the sheet to create a new one (Sheet 9) I would like the new formula to automatically be ='8'!J30.

This is just a sample worksheet. I have got a worksheet with having 3 coloumns A, B & C. Column A contains E Code, while Column B is of time which user will enter. Column C contains the time in Hours.

I have entered one record for example. Now, whenever user enters the value in B3, then formula from C2 should be copied to C3 i.e it should be =B3/60.

I want this to be done using VBA. Pls help me out. I want to use this feature in one of my another files which requries this feature.

Excel 2003. I have a list of names in column A, dates in column B, I need the difference between dates for the people in column A, I am doing it now with the formula shown but I need a formula to put in column C that will look at the name in column A, for that row, and then find that name above it and give me the difference between the to dates in column B. So in the example below if I put the formula in C7 it would look at A6-A2 for harry and then give me the difference between B7 and B4, 6 days, if there is not a match return blank.

Excel 2003ABCD1NamesDateDays Between2tom2/8/123****3/2/124harry3/9/125tom3/10/1231=B5-B26tom3/12/122=B6-B57harry3/15/126=B7-B48****3/26/1224=B8-B3Sheet1

if it is possible te refer to a cell in another worksheet.

I have 10 worksheets in sheet one I put in cel A4 a digit en sheet 2 I put the formula that refers to sheet 1 cel A4.

Now I want a formula that refers to sheet -1 so in sheet 3 the formula automatically refers to sheet 2 cel A4 and in sheet 4 the formula automatically refers to sheet 3 cel A4 without corrections to the formula.

I need a formula to automatically change the summary column according to the month we are in.

Ie:

Last Months Data

Nov

Dec

Jan

Feb

1

8

1

7

4

7

9

2

9

'Last Months Data' column should show Dec. However, as we move into February and I complete the 'Jan' column, I would like 'Last Months Data' to automatically change to show Jan's data - is this possible?

I was previously told to use the following formula but this would automatically select the current months data and not the previous months data which is what i need - =INDEX(B2:L2,MONTH(TODAY()))

Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.

The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.

Copyrights 2005-15 www.BigResource.com, All rights reserved