My Financial year runs from Oct to Sept. My current month is June 2008 and the YTD Expenses is in Cell P2032. I have set up a formula to calculate the prior YTD Expenses as sum(AU2032:AM2032) This is for the period Oct 2006 to June 2007. When I do July 2008, I would like to the formula to caculuate the prior YTD Exp as sum(AU2032:AN2032)
How can I get the formula to Change Automatically based on the Month & Year as per the example shown above?
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 Relatedif there are any Excel Functions for finding X,Y or Z Intercepts. I can't find any online calculators returning the equation of a line in 3D.
View 2 Replies View RelatedI have attached the spreadsheet in a pdf. I can e-mail it to anyone who is is willing to help me.
I am trying to calculate the amount of income per calendar year from each tenant.
I have, for each tenant, a commencement date (the date that the first lease year began) and a termination date (the date the last lease year ended). If a tenant has signed a 10 year lease, for example, and his lease commenced on February 15, 2005, then it will terminate on February 14, 2015. The 10 years in that range each have a different rent for that year. Year 1, (from February 15, 2005-February 14, 2006) he will pay $4k a month. In Year 2, (from February 15, 2005-February 14, 2006) he will pay $6k a month. What I want is a set of columns that show the total rent he will pay in 2005, 2006, and so on. The 2005 total should be: $42,000 (10.5*$4000). In 2006, it should be: $69,000 (1.5*4000 + 10.5*6000), and so on to year 10 (being split between 2014 and 2015. Also, please note that the lease could commence on any day of the month and they pay the corresponding pro-rata share of rent based on the number of days into the month the Lease Year is starting/changing.
The result should look like this:
[2005][2006]
[$42000][$69000]
next tenant amounts
next tenant amounts
writing a formula that would result in my organizations' business fiscal year.
Assuming the fiscal year is 2008, the quarters are as follows:
1st QTR = 7/2007 - 9/2007
2nd QTR = 10/2007 - 12/2007
3rd QTR = 1/2008 - 3/2008
4th QTR = 4/2008 - 6/2008
When 7/1/2007 is entered, the result should be Q1-2008
10/20/2007, Q2-2008 and so forth.
I trying to find a formula to give the difference between two dates in year and month.
For example, the start date is Feb 1 2006 and end date is Jan 1 2008.
The formula should result in 2.11
I tried the following formula from a previous thread but it gives the answer in text but I need it to result in 2.11.
[url]
I have developed a financial calculator that asks the user for the "input date" which is used to record balances as of the input date. My interest calculation for the first year is based on the current date compared to the input date. For example, if the user is keying in a current balance of 10,000 @ 10% interest, and the "statement date" or "input date" is 12/30/2013, and the current date is today, 2/4/2014, then it should calculate interest for the entire year of 2014. It is not doing that. It calculates $3 interest.
But if input date is 6/30/2013 with current date of 2/4/2014, it seems to work OK. It calculates interest of $504 in that case. It appears to get messed up with the year transition between current date and input date. The formulas I have listed below appear to work fine except when the input date is 2013 for the year and the current date is 2014. The formula does not "see" that input date was last year. There must be a minor tweak to formula I am overlooking.
Cell C2 = Today's Date=TODAY()
Cell C3 = Input date (user keys in date in mm/dd/yyyy format)
Cell E2 = "translate input date to year format" =DATE(YEAR(C3),12,31)-C3
Cell E3 = Investment Rate
Cell G2 = yr 1 interest rate adjusted =(E3/1)*($E$2/365)
Cell C21 = Current Balance
Cell D21 = Interest Yr1 = C21*G2
I need the interest calculation to account for partial year accrual.
I need to calculate SUM and AVERAGE of rainfall for each and every year separately and must be displayed separately in a separate column. For your easy understanding, I have done manually and attached the excel sheet.
View 6 Replies View RelatedI have a spreadsheet that tracks “points” for hourly associates on a daily basis that totals weekly and monthly. This spreadsheet works on a rolling calendar year instead of a fiscal and I need to be able to capture the totals for periods of time by days.
For example, I would need to view how many points ‘employee x’ has from 2/25/09 through 2/25/10. I have attached the spreadsheet, which includes tabs for each week ending and a summary page.
I need a formula that will calculate the number of days (ex from 10/1/08-1/1/12) and will differentiate between leap year and a regular year. I am trying to calculate interest between 2 dates and leap year is calculated on a different interest rate. Right now I have it set up so I have to manually type in the leap years in a separate field instead of being able to just you point a to point b.
View 2 Replies View RelatedI would like to fill in a a form on page /sheet one and have the same info on every sheet that follows is it possible?
View 3 Replies View RelatedI have attached a spreadsheet and I am trying to capture the info in lines 2,7,12,17 and return the info into column d,e,f,g
The info in these columns at present has been manually entered but I am sure it could be automated.
OOL Roster Final 18-31Aug14.xlsx
I have two combo boxes: One for entering the Year, and one for the month. I can produce a message if the user leaves either box blank but I want a message to apear it the user selects a year AND month less than the current year (iYear) and current month (iMonth). I therefore need an AND statement between the two criteria but i dont know how to do it.
'....First Checks the Comboboxes arent blank then below Checks a future month/year secection is chosen
ElseIf YearBox.Value = iYear & iMonthbox < iMonth Then
MsgBox ("You may not enter Data before the current Month")
Else '...... Run main code here
I have a macro that when you run it, it creates a new tab with the next month on it. I.e. I'm in January 2014 and I hit the button it creates a tab for February 2014....
In a different workbook, I want to have it copy this newly created tab name into cell A1 of what we'll call Hidden_Tab.
So I want to have Hidden_tab A1 say January 2014, then I press the button to run the macro and it then copies the new tab name from the tab I just created: February 2014 and pastes it in Hidden_tab A1 and so on...
Looking for a formula to compare current year values to previous year values. For example, if the current year has values for the month of January through March (100, 100 and 150), current year value will be 350 and the previous year value will be 975 (i.e. 300+275+400). The aim here is to make the previous year months summation equal to the present (or current) values. As new values are entered for the current year, the previous year's values will have to change to reflect the new month's value entered for the current year.
Month 2012 2013
Jan 300 100
Feb 275 100
Mar 400 150
April 650
May 454
June 800
July 500
Aug 375
Sep 525
Oct. 300
Nov 410
Dec 510
Sample file is attached : Comparison_Years.2011.xls‎
I am working on a budget for myself and want it to have running dates so the first data column will have the current month. I was able to succeed with this using the EOMONTH function followed by EDATE functions in the following cells, I then have these columns filled using a nested VLOOKUP MATCH function pair.
The problem I run into is with the months that extend into the next year, in my data table I have month by month listed started on 01/01/2014 ending 12/01/2014 but as soon as the month is no longer January the last column in my budget cannot find the information needed due to it looking for 2015. so what I would like to know is if there is a way to make the data table change the year to the following year after today is beyond that month, so for example on March 1 2014 both January and February would be changed to 2015.
Attached is an example : Budget Example.xlsx‎
I have dates in my column “A”, for example (A1 cell =22-Mar-1971), (A2 cell=30-Dec-1965). Now my requirement is in B column date and month from A column and year should take current year. Output in B column (B1 cell =22-Mar-2009), (B2 cell=30-Dec-2009)
View 3 Replies View RelatedCould it be possible to "sequentially offset" a vlaue, say, of A1 such that, for instance, when A1 changes from "5" to "6" (via formula, not direct input), A1=6 and, say, B1=5, and so on? You know, like keeping a value record of A1, one step back, in B1.
This would be UNBELIEVABLY valuble to me. Thus, would I be in eternal debt to he/she who would resolve this here.
range C7:AJ7 should look for a 1 in C5:AJ5 and start placing a 4 till 8 cells after the 1.
Range C6:AJ6 will be a little bit more complicated it should place a 2 in each cell, starting from 10 cells prior the 3th last number in range C5:AJ5.
I am mailing a letter on the 15th day and the 45th day after a hire date. The effective date will be the 1st of the month following 60 days. A meeting will be held every 3rd Thursday of the month for all new hires. What I am trying to accomplish is calculating the 3rd Thursday of the month PRIOR to the 1st of every month.
i.e. hire date is 3/11/14 - 1st letter goes out 3/26/14 (15 days) - 2nd letter goes out 4/25/14 (45 days).
Their eligibility date is 6/1/14 (1st of the month following 60 days).
The meeting is held on 5/15/14 (3rd Thursday of the month prior to the first of the month (June))...
HIRE DATE LIST.xls
I have a need to simply rename a workbook without saving. I have a master template named "IR QT" but, within the workbook, I run a macro that saves the file with content from a cell as the filename. After the 'Save' macro is executed, the sheet stays open, as planned, but obviously the file has been renamed. What I would like to happen at the end of the 'Save' macro is for the sheet to be renamed (not re-saved) to "IR QT". I just need the VBA code to put at the end of the 'Save' macro to do the rename back to "IR QT".
View 3 Replies View RelatedI have a macro that selects various cells whilst it runs - nothing unusual there! What I'd like it to do is return to the activecell immediately prior to running the macro. So if cell 'B34' is active and then the Macro runs it currently returns to cell 'A12' (the last selection in the macro).
I would like it to return to cell, in this instance, 'B34'. The activecell could of course be any reference within the spreadsheet, so whatever is active prior to running macro is returned to when macro is complete.
I have checked the following reference whithin Excel VBA's references Manually:
Microsoft Visual Basic for Applications Extensibility 5.3
BUT when I run the following Macro, once checked the prior reference:
Sub AddModuleToProject()
On Error Resume Next
Debug.Print Err ' I GET 0
Dim VBProj As VBIDE.VBProject: Set VBProj = ActiveWorkbook.VBProject
Debug.Print Err ' I GET 1004
Dim VBComp As VBIDE.VBComponent: Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule)
Debug.Print Err ' I GET 91
VBComp.Name = "NewModule"
Debug.Print Err ' I GET 91
End Sub
Is there a way to do this?
For example:
I have a price value that will change constantly stored in the column/row "I45". I want to store that intial price in a cell "A67".
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 am trying to do an IF statement, if the activecell's value is equal to this current year, do nothing and if its not the current year, to offset one column to the right and insert a blank column, then copy the whole column to the left and replace it with the new blank column.
View 1 Replies View Relatedi have the following table of information
Year DOB
7 01.09.96 -31.08.97
8 01.09.95 -31.08.96
9 01.09.94 -31.08.95
10 01.09.93 -31.08.94
11 01.09.92 -31.08.93
and a list of dates i need to look up the date to see which year it falls in and return the year
DATE function won't return TODAY()'s year in the "year" slot.
Is there a way convert, for example, 2/8/1963 to 2/8/2009 without using Concatenate?
I know that in order to draw a chart where a data line for a certain period is compared with the same period the previous year, one should have the 2 sets of data of different year side by side columnwise. However, is there a way where I could still churn out the same line chart when the data is all on a single column?
View 9 Replies View RelatedThe workbook has a tab for each day, and there are variance columns. The tabs are named for the date, like 1.20, 1.21, 1.22, etc.
For example, the formula on 1.21 in D2 is simply =C2-'1.20'!C2
That works fine, but it's a time-consuming job to always change it. You have one sheet to copy many times for all the other formulas, but then have to re-name and change that variance formula on each worksheet.
Is there a way to reference the prior worksheet without using it's specific name?
=C2-'prior worksheet'!C2 would be nice...I could take my one sheet and copy it about 23 times for each workday, then copy that whole workbook to use in the subsequent months without making any formula changes.