YTD Sum Across Multiple Years

Jan 14, 2009

I need to calculate YTD sums using the data that spans three years. I am using

SUM(B2:INDEX(B2:AC2,MATCH($B$10,$B$1:$AC$1,0))). This works fine if all of my data is contained in one year. However, when my data spans multiple years it does not. For example, if I have the following and today is April 2007 I want to calculate Jan-April 2007.

Labor Type09/0610/0611/0612/0601/0702/0703/0704/0705/0706/0707/0708/0709/0710/0711/0712/0701/08
Bus External2404526907569511,2018808059439237781,1216191,0639378001,156
Bus Internal1,4412,7942,9992,5914,0093,9444,2714,3254,7593,5493,0172,4371,9262,9722,3711,7582,931
IT External1,3483,3993,8653,8586,0247,75510,11110,40911,86911,42311,29710,4878,32713,13412,96912,41917,265
IT Internal3,3537,7428,7107,88412,73112,74614,91114,66215,30413,80513,13511,8248,63511,7728,8876,80911,283
Staff Aug12138195169195408796641626340403428469675604395637

View 9 Replies


ADVERTISEMENT

Adding Years To A Date (taking Into Account Leap Years)

Aug 28, 2007

I have a date 07/28/2027 and need Excel to calculate a date 65 years in the future taking into account leap years.

View 9 Replies View Related

Multiple Months And Years In One Cell

Dec 13, 2011

Is there a way to show in excel that something feel within a specific time frame? An example being if I want to see the amount of time it took to close a customer service ticket. I would take the closed date minus the raised date, but then I would like to show in a cell it was open from Feb to Mar 2011.

View 5 Replies View Related

Year To Date Filter - Multiple Years

Jan 11, 2013

Basically I need to add a column to my source data so I can use it as a filter on my Pivot Table in a different workbook. - something just as simple as TRUE/FALSE if the date is YTD for all years would be ideal.

Have attached an example if that makes it any clearer! nice simple formula would be ideal as sheet is around 600,000 rows long and growing!

View 4 Replies View Related

Graphing Multiple Y-axes Datasets ($, %) On Same X-axis (years)

Mar 6, 2009

I have the theoretical set:

year $ %
2003 100 10
2004 120 20
2005 145 30
2006 205 40
2007 300 50

Could I make the year the X-axis and make 2 y-axes on the same graph in excel that each datasets would be correlated to (eg. for money it'd be from 0 to 300 while for percent it'd be from 0 to 50)

View 3 Replies View Related

Macro Using Transpose Function For Multiple Years Of Data

Jul 6, 2014

I need to create a macro in excel that will take data for a particular year in vertical rows and transpose it to horizontal rows.

What the data originally looks like:

380204198401014.72
380204198402016.34
380204198403015.66
380204198404013.71
380204198405013.86
380204198406013.51

[Code] ........

What I want the transposed data to look like:

StationYearJanFebMarAprMayJunJulAugSepOctNovDec
38020419844.726.345.663.713.863.5113.69 3.611.362.812.522.25
38020419853.194.211.021.292.372.416.082.500.143.196.122.04

I have never written a macro before and have tons of records (over 40,000) so using this will save me a lot of time.

View 5 Replies View Related

Best 3 Of The Last 10 Years

Nov 4, 2008

I am working on a retirement calculation sheet. For retirement calculations the employees need to figure out their highest yearly income for three out of their last ten years. The three years can be based on any consecutive twelve months but each of the three years must be the same months, (example, Jan thru Dec, or July thru June). The three years cannot overlap.
Here is what I have so far. Column A, paydate. Column B, amount. Column C, year to date amount. Column D, amount for past 12 months.
I would like to list the three highest years in the first 3 rows of Column E.
Any help is greatly appreciated.

Pay Date Gross Year to Date Gross
Past 12 Months Highest 3 Years 08/10/06 3,594.58 3,594.58 3,594.58
08/24/06 3,332.79 6,927.37 6,927.37
09/07/06 2,595.59 9,522.96 9,522.96
09/21/06 2,457.36 11,980.32 11,980.32
10/05/06 2,457.36 14,437.68 14,437.68
10/19/06 2,457.36 16,895.04 16,895.04
11/02/06 6,212.51 23,107.55 23,107.55
11/16/06 3,206.09 26,313.64 26,313.64
11/30/06 4,104.56 30,418.20 30,418.20........

View 9 Replies View Related

Change Years In A Row

Apr 21, 2009

how to change years in a row after i put in a year in K i have add the file

View 2 Replies View Related

Datedif- Years

Feb 10, 2009

I have 2 dates..

09-feb-09
15-dec-09

Is therea way to calculate the number of years? this is equivalent to 0.27777 years.

Is there a way to get this with the datedif or another functiom?

View 9 Replies View Related

Changing Dates To Years

Dec 15, 2008

I have list of dates:

e.g: D/M/Y

1/03/1997
22/05/2005
13/09/1945

I want a new list that just shows the year and is formatted as a number

e.g:

1997
2005
1945

Is there a way of doing this without doing it manually, I have 20,000 observations.

View 9 Replies View Related

Years And Months Between Two Dates

Aug 7, 2009

The issue is i want years and months between two dates which are not in computer language. Date like 2008/12 and 2010/01. File is attached for you reference

View 2 Replies View Related

Age Table To See If A Player Is Too Old By Years

Mar 25, 2009

i am trying to make and age table to see if a player is too old by years, months and days all i can do is Years i need something like the following

1/10/2009 cut off date birthday is 8/5/1991

all i can do is the following
=YEAR(A3)-YEAR(A2)

View 2 Replies View Related

Concatenating Months And Years

Oct 14, 2009

Please refer to the attached screenshot of my working spreadsheet.

I'm attempting to concatenate lines 86 and 87, month and year together.

The formula I use is: "=C86 & C87" - but I get a large number instead of "October 2009"

View 4 Replies View Related

Dates, Number Of Years

Dec 15, 2008

I have two dates, one is a start date the other is todays date, I want to subtract the start date from todays date and show the number of years. But a small twist is I only want to take the years away from each other, ignore day/month. Start 01/05/2000 todays date 01/10/2008 years = 8. Start 10/10/2000 todays date 01/10/2008 years = 7, want it to still show 8 years and ignore the start day/month.

View 2 Replies View Related

List Only Unique Years?

Jan 15, 2012

I got a column with dates like:

07/01/11
06/30/11
12/31/11
01/01/12
02/14/12
etc.

Now i'd like to populate a combobox with only unique years [i.e. 11 and 12]

View 8 Replies View Related

Calculate When Students Will Be 18 Years Old?

Mar 14, 2012

I have a spreadsheet with my students that needs to find the date a person will be 18. based on their date of birth. I have =B27+(365.25*18) where their DOB is in B27. I have had several instances where it is correct and several instances where it is a day off.

View 4 Replies View Related

Months To Years Conversion?

Apr 4, 2012

I have been trying to figure out a formula to convert the amount of months in a certain cell to years.

I have come up with:

=INT(E29/12)&" years "&MOD(E29,12) & " months"

The problem is it comes up with 2 years 3.37063951499227 months

How I change it to come up with 2.4 months, round it to the highest number instead of loads of decimal places? I have tried change cell E29 do 0 decimal places but it doesn't work.

View 2 Replies View Related

Calculate A Quote Over 4 Years

May 24, 2012

I need to calculate a quote over 4 years but the first and last years are not full 12 months.

On Row 20 are monthly costs for each year. The start and end date is in Row 3.

I need to find a formula that can calculate ANNUAL TOTAL for the all these year in Row 22 populated automatically, not just manually times the months in each cell.

Here is a sample file.

View 9 Replies View Related

Printing To Labels For Years

Apr 27, 2008

I created a wedding list with a bunch of fields for each household: first name person 1, second name person 1, first name person 2, second name person 2, street #, street address, apartment, city, state and zip. Then I realized that I probably needed to combine fields for each line of the address so I created 3 combined fields: 'combined 1 ' that looks like this "joe smith & patsy cline," a 'combined 2,' that looks like "14 jones street, #3," and a 'combined 3,' that looks like "New York, NY 10037."

I haven't printed to labels for years and when I did some quick research via the help function on excel it said to print labels via word...which worked fine until i got to this section: " In the Mail Merge Recipients dialog box, click any column labels in your data that correspond to the Word identifiers on the left. This step makes inserting your data in the form documents easier. For more information about matching fields, see Word Help. "

I have no idea what this means, but here's what I need to figure out: " how do I print labels using just 3 of 8 existing columns in my spreadsheet, i.e I just want to use combined 1, combined 2 and combined 3 for the 3 lines of the address. My first instinct was just to cut the contents of the combined fields into a new spreadsheet and just do it taht way, but I created the combined fields using a formula which relates to the other fields........

View 9 Replies View Related

Totaling Years In Cells

May 20, 2009

In Column B I have various dates i.e [01/02/2008]. I Need a formula that will count the number of times the year 2008 appears in column B.

View 6 Replies View Related

Subtract X Amount Every Y Years

May 28, 2009

I am building an investing strategy model and am looking for a fomula or combination of formulae that would subtract an amount (lets say a 100) every so many years (lets say 10). Data is set up horizontaly, i want to be able to set how much will be subtracted and how often in a "control panel" next to other inputs and variables. I am not proficient in Macros and most often have trouble with them so if its possible to solve this without using any that would be great.

View 9 Replies View Related

Find How Many Jan 31 Are In A Range Of Years

Jul 4, 2009

I have the following:

A2= 01/20/2000 B2= 02/05/2000
A3= 09/05/2001 B3= 10/15/2008
C2 and C3 is where the answer will be.

what formula can be used to find out how many January 31th has passed between those ranges of years?

View 9 Replies View Related

Creating Rows Of Years

May 11, 2006

I'm wondering how you can quickly create a row of labels holding Year 1, Year 2, Year 3, etc. I used to know how but I can't seem to remember anymore and can't find how to anywhere.

View 3 Replies View Related

Calculate Age In Years And Months

Oct 5, 2006

I was asked by a colleague how they could put a persons date of birth in one cell, todays date in another, and return their age in years and months. Accuracy to within a month. This is what I gave them.

=TRUNC((B1-A1)/365.25)&" Years "& ROUND(((B1-A1)/365.25-TRUNC((B1-A1)/365.25))*12,0)&" Months"

View 4 Replies View Related

Advanced Filter Between Years

Dec 4, 2006

I have a list of subscribers, each with an account id and the years for which they have subscribed. Each account id can be listed up to five times. I am trying to find out how to use advanced filter(or some other way!) to find those accounts that were subscribers in any of the previous four years but not the current year.

View 9 Replies View Related

Years & Months Between Two Dates

Apr 12, 2007

Which formula should I use to return years and months between two dates.

4/1/05 7/30/25

View 2 Replies View Related

YEARFRAC & Leap Years

Jul 30, 2007

It looks like a pretty silly problem but it's driving me mad. The yearfrac function takes a two dates and a basis. Now assume basis = 1 ( Act / Act ) and two cases:

1) 06-Jan-06 -> 06-Jan-08 ( 730 days ) = 1.998175182 =>> divisor = 730/1.998175182 = 365.33333

2) 06-Jan-05 -> 06-Jan-07 ( 730 days ) = 2 =>> divisor = 730/2 = 365

In case 1) 2008 is a leap year ( but the "leap" has yet to come! ). Can anyone explain me the logic behind? I suppose the extra day is divided by 3 but where is the "Actuality" of the divisor?

View 4 Replies View Related

Formulas That Distribute Days Across Years?

May 16, 2014

I need to create variables that splits out a length of stay across years and enters the number of days in each of those years into separate year variables. Below are two sample cases:

CaseID StartDate EndDate
222 12/23/1988 01/01/1997
22112/25/1989 2/12/1990

After creating different variables for the different years, in this case: 1988, 1989, 1990, 1991, 1992, 1993, 1994, 1995, 1996, 1997, I want to be able to pull out the number of days that goes into those years as separate variables.

Thus

caseID daysin1988 daysin1989 daysin1990 daysin1991 . . . . 1997
222 9 365.25 365.25 365.25 0
221 742

and so on

View 3 Replies View Related

Comparing The Same Month Sales Between Two Different Years?

Jun 6, 2014

I am trying to create a function where the user can type in a single month, and 2 different years, and it will calculate/compare that single month from the 2 different years entered.

For Example: I type in January, and I type in 2014 and 2013. The excel formula will spit out whether January 2014 is over/under January 2013.

See attached for my sales report. You can see where I left some space for the requested function.

as it is the last change I have to make to the sales report.

View 5 Replies View Related

Find Average Pricing For 16.5 Years?

Jul 31, 2014

Ive been given the data for bond pricing for two tenures i.e. 15 years and 20 years . I needed to find the average pricing for 16.5 years , whether excel is able to compute such pricing?

View 5 Replies View Related







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