The last part of my formula is throwing an error "K2:K57"
What I should get in plain English.... "The number of 'James Report' 'Completed by 'KP' in 'August'. (Any specified month based on what comes from my "dates field" on my Master sheet.
I have got a field in my Master sheet with dates which I have transposed into the right month of the year using Month().
I have a column titled "Start Date", "Month/Year", &"Total". I want to count the number of dates that appeared in the "start date" column that have the same month and year of the "month/year" column. That count should be in the total count. For ex, I want the first total (april 2008) to be 1, the second (may 2008) to be 1, and so on. I tried using countifs month and year of the start date is the month and year of the month/year column. Not working.
I am trying to use a countifs function that counts all the instances where the year of the date is 2014. The dates are in date format like 3/13/2013, and contained in cells a2:a12
Here is my current formula which is not working out for me.
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)
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.
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?
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.
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.
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)
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.
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.
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.
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.
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.
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.
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.
basically I am trying to total this spreadsheet up to work out a sum for the month by customer number. some customers made a payment in jan 2012 and paid again in jan 2013, some paid twice in one month.
what needs to happen is
it needs to have customer number going down and the month/year going across the top with a summary paid for that month
