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!
Need to create year to date sales comparing 4 years month by month. Stacked chart (Excel 2010) works OK for the first three months but adding the fourth month changes the chart to 4 series with a monthly axis. To put it another way I need a vertical axis of years and a horizontal axis of $$$ with each months sales of each year stacked on its year.
View 10 Replies View Relatedhow to filter by the year only in a column where I have dates specified as dd/mm/year.
This is being done through VBA and I tried the following (relevant code posted), but it doesn't seem to be working:
[Code].....
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 RelatedWork has given me a hourly sales data for the last 2 weeks, and Last years hourly data for the equivalent 2 weeks.
The question is compare the average of the last 2 weeks against the average of the equivalent 2 weeks last year and evaluate if the trading performance is majorly different.
What formulas to use? Should I use percentages or leave it?
There are two columns in an excel sheet, one is date of birth and other is date of reteirment
looks somewhat like this:
D_O_B D_O_R
5-Mar-53 31-Mar-11
30-Jun-57 30-Jun-15
20-Jun-51 30-Jun-09
2-Feb-55 28-Feb-13
2-Jul-51
13-Oct-55
1-Sep-51
7-Jul-54
14-Mar-53
3-Aug-50 3 0-Sep-13
Some of the dates in D_O_R are missing. I need to fill in up all the dates in D_O_R column.
D_O_R is = 58years+D_O_B(NOTE the dates should come as last date of the month)
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‎
I have a list of people with 10 years of salary history for each (in ten consecutive columns on the spreadsheet).
I need to calculate the HIGHEST 3 consecutive year average salary for each (if they have less than 3 years with salary, then it should just average the years the do have, be it 1 or 2 years).
Here is the kicker: some people have breaks in service (for these years, there is a blank in that entry). These years should be ignored and skipped in calculating the avergaes.
So if someone had salary figures in years 1, 2, and 4, but a blank in year three, the average of years 1, 2, and 4 would constitute one three year average (whether or not it is the highest is a whole other matter...).
I have been round-and-round the best way of doing this. I was thinking of maybe creating a UDF that calculates a three average, then do it up to 8 times (one for each starting year) in 8 "helper columns", and taking the highest average.
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 RelatedI have mutliple non-contiguous columns that are SUPPOSED to contain dates. If the particular item for that coulmn doesn't require a date then "waived" is typed; others may be blank. I want to apply a filter that will tell me if any one of the columns YEAR is older than "2004". I'm really bad at formulas to bare with me.
I inserted the following formula: =IF(OR(YEAR(N6)<=2004,YEAR(V6)<=2004,YEAR(X6)<=2004,YEAR(AB6)<=2004,YEAR(AD6)<=2004,YEAR(AF6)<=2004,YEAR(AI6)<=2004),"EXPIRED","GOOD")
The problem is that if the cell is blank or if contains "waived" it either produces "#value!" or it reports "True" when it is actually "False". I'm not sharp enough to know how to account for those two conditions in my formula.
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 RelatedThis 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 six columns.
Column A: dates
Column B: data with relates to A
Column C: dates
Column D: data with relates to C
Column E: dates
Column F: data with relates to E
In column A, I have dates; In column b i have security levels. I have made a table called "Security" it contains to columns, a list of security levels and no of years when each security level is required to be reviewed. the table is setup -
d1 e1
Restricted 5
etc
Example of data
ie.
a1 b1 c1
Restricted 1/06/2012 1/06/2017
What I am looking for is a formula to look up a1 "restricted". then lookup the security table and find "restricted" its value is 5 (years) then add the 5 years to date in b1, but place it in c1.
i 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 have a sheet in my workbook with at least 180 small tables, there may be more.
I woulds like to be able to change total formulas for all tables at once to show either year-to- date or total year.
For example:
If we have only progressed through the second period of the year, I would like to choose something to indicate period 2. At other time I may want to know the total year whether the periods are completed or not.
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
Right now, in a column I would like to display an number (length of employment) based on the hire date.
In one cell the employee's hire date is entered. In a column of other cells the pay period ending date is displayed and in another column the length of employment is displayed.
Example:
03/17/2000
03/01/2009 8
03/15/2009 8
03/29/2009 9
04/12/2009 9
04/26/2009 9
How would I create a formula in the length of employment cells that would indicate the correct number of years based on the hire date and adjusts when the pay period date passes this hire date?
I need to auto generate and populate some cells with todays date but three years from now.
View 3 Replies View RelatedIs 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 RelatedI would like to calculate the number of years and months that have passed since a certain date. Would like it in a number format so I can pickout those who have gone reached 5 year increments during each month.
Such as someone reaching 40 injury free years in June of this year I can let them know.
Serial No Search E220060926320061125420060612520070824620061026720061226820061127920061226 Excel tables to the web >> Excel Jeanie HTML 4
E - Year Month Date
I need F column as Month Date Year Format
I have some cells which must be in the format 15/06/2007 15:25
I then need to add either days, months or years onto it.
Say the above date/time is in cell A1, when I do =YEAR(A1)+5 it displays 2012 if I choose the general cell format, but when I select the same cell format (date time) it comes out as 04/07/1905 00:00
I just got into the world of PowerPivot, Excel 2013 and Pivot Tables and am in the process of creating a Dashboard which I will then be uploading to SharePoint 2013.
On top of page I added the new timeline filter which I've linked to my pivot charts. Now what I would like to do, is create an additional pivot chart which looks at whatever date range has been used in the timeline filter and subtract 5 years from that. So, when I select a date range of November 2013 - December 2013 in timeline filter, the additional pivot chart will show the details for November 2008 - December 2013. This is where I get stuck.
I'm using two SSAS cubes which I'm combining together in PowerPivot and then display in Pivot Tables and Pivot Charts.
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)
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.
I have a forecast monthly trial balance sheet and an Income Statement Analysis sheet. I am analyzing the Year to Date performance for Dec-2013. I need a formula that will match the income statement line i.e revenues, accounting expenses etc. and then sum horizontally from Jan-2013 to Dec 2013 ( if YTD month' Dec-2013' is greater than or equal to date range then sum horizontally the corresponding income statement lines up to the reporting month).
View 2 Replies View RelatedI have a document that I downloaded some data from the Internet "Kronos" and within this data there is a column for date. This column is formatted as Date (01/08/13), but it only displays the date as Sun 1/01. It's missing the year, which it should be 01/01/12. I have tried to use TEXT function to convert it to month name and day name using TEXT(N4, "mmm") and Text(N4,"ddd"), but I still getting Sun 1/01. I have also tried to use Text to columns to split this date but, it does not work. Is there any other way I can convert this date into m/d/yy?
View 2 Replies View Relatedwhat formula could I use when I type in a date another cell rounds up to Jan 1st of the next year? ie 2/1/07 & in another cell it returns 1/1/08.
View 3 Replies View Related