Formula For Counting Back A Year?
Jan 17, 2012
know if there is a formula for counting back a year from a date?
Basically what I have is we operate a rolling absence/ sick year and so what happens is on a staff members return from absence/ sick we look back exactly one calendar year from the last day of absence and count the number of days off absent in that period for comparison against their paid allowance.
I have posted a mock up sheet and what I am looking for is a formula for column C. The highlighted cells are the period it would be if today was the last day of absence.
Sheet1 Â ABC1Days AbsentDateRolling2201/01/2010Â 3302/02/2010Â 4403/03/2010Â 5207/06/2010Â
6310/11/2010Â 7431/12/2010Â 8601/01/2011249202/02/2011
2410103/03/20112211307/06/20112112510/11/20112413201/01/20121914317/01/201216
View 5 Replies
ADVERTISEMENT
Nov 27, 2012
I'm using the following to return the previous TUESDAY in my Event Date cell F43
=$F$43-WEEKDAY($F$43+4)
It works great but if my event date is a Wednesday I need to return the Tuesday in the week prior not in the Tuesday immediately prior.
That is, If the event falls on Wed 22 May 2013 the cell needs to return Tuesday 14 May 2013 not Tuesday 21 May.
View 3 Replies
View Related
Dec 8, 2013
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‎
View 4 Replies
View Related
Dec 15, 2008
Need a formula for counting the number of occurences of a month & year in a date column? The spreadsheet is looking at items raised in any given month e.g. all items raised in Dec-08.
View 3 Replies
View Related
Feb 3, 2014
I was given this spreadsheet to count attendance by entering the entry date and exit day, however it's counting the first day and the last. I'm needing it to only count the first day and not the exit day.Book2.xls
View 1 Replies
View Related
Mar 1, 2009
Is it possible to make a back-reference in a cell formula similar to regular expressions?
Example:
=IF(I15=FALSE, [H17 * INDEX(Percentages,I17+1)], IF(A17="", $1, 0))
...where [ ] indicates the actual reference and $1 indicates the reference number
View 9 Replies
View Related
Aug 11, 2012
I have a layout something like the following:
A1
A2
A3
[Code]....
Where each (i.e., A1) represents a location. I have tried to use a coordinate system but this will not work for the back-to-back locations. (Assuming each location is 2 feet wide, For example A1 to C1 is 4 feet apart, not 2 feet (as Euclidean or rectilinear would calculate it as).
Would there be a way to incorporate an if statement for those locations that are back-to-back? As a rectilinear distance calculation would work as long as the locations are not part of the same "block".
Ultimately I am looking to have a matrix which contains all the distances between each location:
A1
A2
A3
B1
[Code].....
View 4 Replies
View Related
May 30, 2014
Here is my formula:
=IFERROR(INDEX(Stored!$D$22:$AN$795, MATCH(D11,Stored!$E$22:$E$795,0), MATCH(E10,Stored!$C$22:$AN$22,0)),0)
For some reason it keeps pulling back the value that is one cell to the right, so one column off.
What could it be.
In this example it should pull back N363 and it is pulling back O363.
View 1 Replies
View Related
Jun 5, 2007
Name Qtr Year
Org 112007
Org 222006
Org 332006
Org 442006
Org 542007
Org 612006
I want the code to say, If Qtr = 1 And Year = 2007, then bring back Org Name, If Qtr = 2 And Year = 2006, then bring back Org Name and so forth.
The code should loop through a range of constant data (Region Name), until the Region Name is blank.
Dim OrgCell As Range
Dim OrgTargetCell As Range
Dim RegionCell As Range
Set OrgCell = Sheets("DATA Removed").Range("A2")
Set OrgTargetCell = Sheets("Overdue").Range("A3")
Set RegionCell = Sheets("DATA Removed").Range("C2")
i = 0
OrgCell.FormulaR1C1 = _
"=IF(AND('DATA Removed'!R[-1]C[11]=1,'DATA Removed'!R[-1]C[12]=2007),'DATA Removed'!R[-1]C,0)"
Do
OrgTargetCell.Offset(i, 0).Value = OrgCell.Offset(i, 0).Value
i = i + 1
Loop Until RegionCell.Offset(i, 0).Value = ""
End Sub
View 9 Replies
View Related
Aug 26, 2009
cell AT7 is a formula which brings back a numerical value i.e. 15.
in cell AS7 i need a formula that displays the value of cell AT7, as long as that value is less than 15, if the value is 15 or higher then 0 should be displayed.
I dont know why i cant get this, seems like a simple IF statement to me, but who knows.
Once this formula is done i need to drag it down about 2500 cells and autosum to get a column total...
View 3 Replies
View Related
Nov 11, 2009
I’m trying to send an array of values INTO a user defined function, do a little math on it, and then send the resulting array back to the caller. The caller in this case is an array formula in an excel cell.
I can get it to build an array after the math, but I can’t get it to pass the resulting array back to the formula. The following snippet is a simple version of the code. Here I am building the incoming array in the macro, but same difference at the end. The outgoing Oil_spgr variable never seems to contain the full, final array.
View 4 Replies
View Related
Aug 26, 2009
I was wondering whether there is an easy way to change a formula containing a named range back into it's original cell reference in VB. For example I have a cell saying =cogs2008/revenue2008 and I want to change it back into =A2/A3. Any ideas?
View 9 Replies
View Related
Mar 24, 2009
I have a spreadsheet that each month, we populate a new row of data. The rows are already set up in the spreadsheet, but we just populate the new row.
We are calculating a rolling 12 month total. Each month, we have to modify the formula below to pick up the last 12 months.
For example, next month we will populate data into cell M91, then we need to manually modify our formula to read M80:M91. Wondering if there is a way to have the formula below to look at a range, such as M100:M1, and count the last 12 months? This would eliminate us having to change this each month on several spreadsheets.
In Summary: I would like to replace the M79:M90 to count the last 12 months instead of changing the formula each month.
Here is the formula:
=(FVSCHEDULE(1,M79:M90/100)-1)*100
View 2 Replies
View Related
Feb 16, 2014
I've got a forecast from a customer and need to summarize it with part number, quantity and date.
The spreadsheet is part no in column a due dates in row 1 values at the intersection of part no and due date and i don't want 0 quantity to report back.
my output needs to be partno, date due, quantity.
View 4 Replies
View Related
Jun 4, 2014
=MIN(IF(B3:B32="",IF(A3:A32>0,A3:A32))) ArrayedOldest Date Array.xlsx
Following on from a previous post need to add to my (forums) array.
I need to find the location of a date which has been looked up in a cell based on the above formula which is in the attached.
View 3 Replies
View Related
Jan 17, 2010
Trying to do a year to date formula with my numbers going across with actual,goal, percent to goal.
I want to have the ytd auto sum based on my As of date that I did a list validation for. Can't get it to work and getting very frustrated - can anyone held with this?
I attached a worksheet with the YTD listed on the far right.
View 13 Replies
View Related
Jul 15, 2008
Is there a function that will return the day of any particular year?
For example, if the date 01/01/2008 is entered, it will return 1 and if 31/12/1977 is entered, it will return 365?
View 9 Replies
View Related
Oct 22, 2008
i need to put a date into a month +year
like this:
1/06/2008jun/08
i did this with a format date.
After that i tried to copy/paste special values. So i can make i pivot table on the jun/08. But the problem is he still see it like a date.
MaandTotal
jun/081jun/0810
he sees that 1/6/2008 is not the same as 2/6/2008
that is why i got 2 lines of jun
but i just want one and that he counts all the junes together
View 9 Replies
View Related
Sep 25, 2009
I could use a little help with a leap year (they sure are great, eh?) formula.
There's a cell (A1-K1) with a year in it (2008-2018)
The part of the formula I need is something like this:
=IF(A1 has 366 days,366,365)
So I can put this at the end of my formula.
Should be simple, but my 1044-page Excel 2000 selfhelp Bible can't offer me the solution.
View 9 Replies
View Related
Aug 3, 2006
I would like to have an IF statement which if true gives a result of a date plus 365 days but if false gives a prescribed day and month but uses a year from another cell.
I was trying something like this : IF(J5="y",E5+365,01/04/YEAR(E5)) Where E5 gives a start date of a project in a dd/mm/yy format and I would like this to give the same dd/mm/yy format.
Does anyone have any suggestions - the first part of the statement should be fine it is really the year element that I am having trouble with.
View 3 Replies
View Related
Sep 25, 2006
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
View 3 Replies
View Related
Jun 5, 2014
I have a sheet where the columns have the month end date for each date, ex January 31,2014 February 28, 2014, March 31, 2014 etc. These dates are used in another formula to compare to the current date and if the current date is past the result is different from when if the date is in the future.
I would like a formula that would update the Month end date when we enter a new year. So for example once we get to January 1 2015 my date would change to January 31,2015, February 28, 2015, March 31, 2015. this would note be dependent on the system date but on the date in another cell.
View 3 Replies
View Related
Jun 28, 2014
I have a spreadsheet that consists of date, product number with amount received against each date.
What I need are formulas that will automatically provide me with:
1. The sub-total monthly income for each product against each financial year.
2. Total income for each product by financial year.
3. Total income for each financial year.
View 11 Replies
View Related
Dec 9, 2013
The data in my spreadsheet will have a column of dates that will change depending on info input in another cell.
What I need is a formula that will give me the most recent date for lets say 2012
04/01/13
03/01/13
02/01/13
01/01/13
12/27/12
12/25/12
12/12/12
12/02/12
if use the max function I get the 4/1/13 result, what I need is a formula that will return the 12/7/12 number.
View 5 Replies
View Related
Feb 4, 2014
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.
View 3 Replies
View Related
Dec 13, 2012
The formula that I currently have in E2, is giving me the number of years served by an employee. Is there another formula that can give me the number of years each employee has served? This is the formula that I have in E2
[Code] .....
Attached File : VACATION DAYS ACCURED.xls‎
View 9 Replies
View Related
Mar 23, 2009
finding a code that would automatically insert various phrases into a column depending on whether or not the person in that row was due to renew their membership in our organization. The code I received works fantastically, but I've come across one snafu:
View 3 Replies
View Related
Jan 28, 2012
Inmates can apply for pre-release when they have half their minimum sentence served. For example 2 years 6 months. They would have to serve 1 year 3 months.
I use this formula
=DATE(YEAR(A1)-A5,MONTH(A1)-B5,DAY(A1)-B6)
Where A1 is half the years, B5 is half the months, and B6 is half the days. The problem is when an inmate is sentenced to a minimum of 3 years and 3 months. The latter formula will not calculate 1.5 years or 1.5 months. It rounds up. What I would like to do is covert the 1.5 years to months and the 1.5 months to days.
View 5 Replies
View Related
Dec 18, 2013
So I use this formula on one of my task sheets to do budgets. Now the problem is we are entering 2014...yet the formula won't go into the next year without some sort of year scan:
Code:
=INDEX('Template Sheet'!$B$1:$S$233,MATCH($A11,'Template Sheet'!$B$1:$B$233,0)+2,LOOKUP(MONTH(DATEVALUE($B$8&" 1")),MONTH('Template Sheet'!$D$1:$S$1),COLUMN('Template Sheet'!$D$1:$S$1))-1)
that is the first formula but I need it to look up the year also and everything I have tried has failed miserably. So basically when it is matching with the month I have a drop down that has Jan-December...but I need it too look to the Feb 2014 not Feb 2013...How can I add the year section?
View 9 Replies
View Related
Mar 22, 2005
I am experiencing a big block on this one...I am trying to find the sum for a group of cells in a particular column given that MONTH and YEAR (as stated in two separate columns) match the date that is displayed in, lets say for example, A2. below is a sample of the data I am refering to.
************************************************************************>Microsoft Excel - Investor_Portfolio_TEMPLATE.XLS___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA12=
ABCDEF1DateAmountYearMonthSumofAmount23/21/05990002005Mar33/21/0599000Apr46/8/0593000May56/9/0599000Jun63/23/0599000Jul73/23/0599000Aug81/4/0699000Sep91/5/0699000Oct101/6/0699000Nov112/1/0699000Dec122/5/06990002006Jan134/5/0699000Feb144/7/0699000Mar155/2/0699000Apr165/8/0699000MaySheet2
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.[/url][/code]
View 9 Replies
View Related
