Sum Year To Date Based On Month Chosen, Rank Values & Compare Rankings
Dec 22, 2008
1. I would like to be able to select a month from a drop down ( cell C4), and for Column B ('Cumulative Performance') to reflect the sum for each name between Jan and the month selected.
2. In Column D I would like to rank the relative position of the sum total; such that if I selected 'Dec', John would display '13' in D7, Anne '3' etc.
3. In Column E I would like to show by way of a coloured arrow (or even a smilie icon) the relative change in ranking of the sum totals evaluated for my chosen month with those calculated up until the previous month (e.g. for Anne, if I select June, the Jan to June total is 36 (rank 2 in the June total's), the May to Jan total for Anne is 32 (rank 1), therefore her relative rank movement between the June and May cumulatives moves down and cell E8 would show a red-down arrow (amber horizontal for no change and green up-arrow for an improvement in rank).
View 5 Replies
ADVERTISEMENT
Dec 6, 2009
I want to calculate Year To Data in B1 based on some data in C1 to N1. The monthnumber is located in cell A1.
There is of course several ways to do this, but is there a simple and easy formula one can use.
View 3 Replies
View Related
Jan 21, 2009
formula off here i use all the time relating to finding and sumproducts for specific months and years i.e.
Jan 2008, Dec 2007.. depending on these dates excel searched through a specified range and returned me any values i wanted like No. of occurences, totals, sums etc etc it was a sumproduct formula...
is there any way i could specifiy a date i.e. Jan 2009, which would search column a and return the date /and/or an account number in column b, only if the date was during jan 2009?
The reason i want this is to use a lookup on the account numbers to return specific items of info, but i only need the account numbers if they occurred in specific months which i want to choose.
View 9 Replies
View Related
Jan 4, 2014
How I can subtract a year when I formulate based on if the current month is January.
Such as Current month = January 2014, I would require the cell to populate December 2013, any other month would return the current year value.
I have currently tried { =DATE(YEAR(A3)-1,MONTH(A3),DAY(A3)) } whereas cell A3 uses NOW() function formatted to MMMM, but it changes for every month.
View 9 Replies
View Related
Jun 1, 2014
I have my ms access and ms excel which is connected each other .
=> now in my ms access have a table name (tblMonth) has columns (year,MonthNum,StartDate,EndDate)
HERE: sample in january(1) and feb(2)
YearMonthNum StartDate EndDate
2014 130/12/201305/01/2014
2014 106/01/201412/01/2014
2014 1 13/01/201419/01/2014
2014 120/01/201426/01/2014
2014 127/01/201402/02/2014
2014 203/02/201409/02/2014
2014 2 10/02/2014 16/02/2014
2014 217/02/201423/02/2014
2014 224/02/201402/03/2014
=> and for my ms excel has its columns (year,month,date) in this if i input the date that is base in the database range from StartDate TO its EndDate will automatically put the month and year which base also in my database (year,MonthNum)
HERE:
A1 B1 C1
Year Month Date
A2=2014 B2=January C2=01/01/2014
View 3 Replies
View Related
Aug 20, 2013
How to get only MONTH' YEAR if there is a DATE-MONTH-YEAR in a cell?
A1: 27-July-2012
Answer D1:JULY' 2012
Pl note "' " is suffixed after JULY (the month).
View 4 Replies
View Related
Oct 27, 2012
Need to rank by score1 in descending order and Serial # in ascending order. My biggest problem is to get the rank number to be consecutive. I have listed the rank I would like to see.
Score1
Serial #
Rank
90
146168
1
[Code] .....
View 5 Replies
View Related
May 14, 2009
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 Related
Jul 28, 2009
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
View 9 Replies
View Related
Mar 8, 2014
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‎
View 4 Replies
View Related
Dec 25, 2013
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 Related
Dec 17, 2009
In attached sheet, I am trying to find total cost by month only for year 2009. Currently formula I have in Cell c24, is {=SUM(IF(MONTH(B2:B9)=1,D2:D9,0))} But this calculates for all years, not just 2009. How do I modify above formula, so for each month, it shows total cost but only for 2009?
View 2 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
Aug 15, 2014
I have a table with columns 'Date', 'Account', 'Sales'.
I need a formula that will give me the given sales for a certain month, for a certain account.
Ive tried various SUMIFS '=SUMIF(Table1[Billing Month],(MONTH(A191)&YEAR(A191) = MONTH(Table1[Billing Month])&YEAR(Table1[Billing Month])),Table1[Net Invoice Value])'
Ive looked at Arrays =SUM(IF(MONTH($A$2:$A$6)=1,$B$2:$B$6,0))
View 1 Replies
View Related
Oct 14, 2008
I am trying to use a pivot table to analyse some data. The base contains a "day" date..ie 01/05/08, however I want the pivot table to summarise by the month..ie May 08. I have used the "=month(cellref) command, and it returns the correct month number. However when I try and format this to "mmm" or "mmm yr" to get a month I can then cut and paste, it always retunrs the month of JAN.
View 3 Replies
View Related
Apr 24, 2009
i have a column in a spreadsheet that stores a date and another column that i want to store a date category. I want the "date category" column to return the date from the "date" column as: eg Jan 09 (if the date is eg 24/01/09)
View 2 Replies
View Related
Aug 17, 2009
Im having a little issue with the way the dates are layed out.
I have a condition format =AND(RC5="No",TODAY()<RC4+10)
And withen the cell's the date is located, If the cell is writen Month/Day/Year everythink works.
Though i have always written dates like Day/Month/Year.
ive tryed to do go to the number format thing and change it, though that just seams to change the end display not the way excel handles the date. it there a system level change that can be made of change somethink.
View 6 Replies
View Related
Apr 29, 2012
I'm getting people to set up the dates as mmm-yy which makes it so much easier to run a macro with dates. However I still need to search on d/mm/yy (e.g. 1/04/2012 for April). Is there a way of allowing the input box to have April 2012 match 1/04/2012?
I've currently got a message in the Input Box specifying that people need to type in the specific first day of the relevant month.
View 3 Replies
View Related
Sep 15, 2013
I have a list of dates that has been entered as dd/mm/yy. I need to extract the month in one column and year in another column from that date.
I tried using =Month(a1) and Year(a1) but it returns as #value.
View 8 Replies
View Related
Jul 10, 2007
I have data which I import from CSV files from a JDE application on a monthly basis, using "JobCostData" macro ( have not posted code here as it is contained in the attached file).
For each month I import the data my macro creates a seperate spreadsheet and names the spreadsheet as the "mmm-yy" the data relates to, and then takes the information from each sheet and collates this on to on spreadsheet named " Pivot Data".
I then run a second macro "MacroXX", ( again have not posted code here as it is contained in the attached file), this macro looks at the data on "Pivot Data" and creates a unique list of Work Order Numbers and Descriptions and copies them to the spreadsheet named "Report".
Now what I would like to do (and can't get my head around):
Once the "Report" has been populated I would like to come up with a series of total "Cost $" for each Work Order by:
1. Month, for the past 6 months based on a Calendar Month End date (Note: For now I have hard coded the dates, but I will add code at a later stage to prompt user to select a date which will then write this to cell H4 on the "Report" spreadsheet and have the other dates derived from this date.)
2. Year to Date (YTD) based on the Financial year the Calendar Month End Date falls
3. Inception to Date (ITD) based on the
Note:
1. I have tried using Pivot Tables but this will not give me ITD totals.
2. I would prefer to use a macro rather than formulas.
3. I have populated the "Report" spreadsheet with the expect result based on the current data.
View 3 Replies
View Related
Jul 14, 2007
I would like to see the Year To Date sum month by month in cell B8 when rolling down the cell B6. See in attached file.
View 9 Replies
View Related
Jan 9, 2013
I have a spread sheet for 12 months. I am wondering if there is a way to format to show in the cell only day of the month.
View 4 Replies
View Related
Mar 10, 2009
I am creating a worbook to generate month end reports. I'd like to pull figures from the various budget sheets into the reporting sheets for the current month and year to date by setting up a variables sheet and I think the "Choose" function but my experimentations have proven me incapable of doing so.
What I'd like to do is set my month in the variables sheet to the month I want to report in, eg "Feb" and have the budgets pull through automatically.
View 4 Replies
View Related
Dec 2, 2009
i have attached a sheet, i want if i put date in cell a2 and automatically month should come in cell b2 and year in c2,
LIKE JAN AND 2009 LIKE THAT
View 5 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
May 3, 2009
My workbook has a worksheet that lists service activities and a 2nd worksheet that contains a month-year column and an associated Customer PO# to be assigned to an activity based on its Open Date...if it is a specific type of activity.
My logic statement is IF activity type RO is "X", then match its Open Date to the Month-Year in the AssignPO# sheet and assign the associated Customer PO.
I've attached a sample workbook to make it simpler to understand.
View 8 Replies
View Related
May 15, 2012
I am trying to show the date in a spreadsheet as the month and the year.
The date is in B2 30/04/2012 and i want it to show in C2 as Month/Year.
I am currently using =month(B2) which shows it as a no ( 4 ). Can i make it show as month / year?
View 4 Replies
View Related
May 3, 2008
I am trying to get mmm-yy date format for the next 2 consecutive months from now. I used the following code for that reason, but I am not getting.
strDate = Format(DateAdd("m", 1, Date), "mmm-yy")
strDate = Format(DateAdd("m", 2, Date), "mmm-yy")
However if I use this, I get the current months date correctly (like May - 08)
strDate = Format(DateAdd("m", 0, Date), "mmm-yy")
View 9 Replies
View Related
May 28, 2008
In Column A, I have a date formatted as follows: 10/13/2007
I want to break this out into three other columns as follows:
Column B Month = 10
Column B Day = 13
Column B Year = 2007
I need all three columns to be values that I can use in a VLOOKUP.
View 4 Replies
View Related
Feb 26, 2012
I have 1 cell where i am entering month like 2 (for FEB) and in other cell i am putting value 2012 (year)
I have 1 more cell where i have to manually add 1st date of the month like: 01-02-2012
Can I merge first two values and let excel automatically return 01-02-2012 in my desired cell.
Also if i need to convert 02 to FEB or 03 to MAR then what is the formula for this.
View 6 Replies
View Related
