So, this works perfectly by itself: {= SUM(IF(MONTH(I4:I17)=1,G4:G17,0))}
And this works perfectly by itself: {=SUM(IF(YEAR(I4:I17)=2007,G4:G17,0))}
But this doesn't work at all: {=SUM(IF(AND(MONTH(I4:I17)=1,YEAR(I4:I17)=2007),G4:G17,0))}
SUM by both a specific month, and a specific year from a single date field?
In a sheet I enter the following:
... in A1 a year (say 2012)
... in A2 a month, formatting as "MMM" (JAN, FEB, MAR etc.)
How to automatically get in column A (say from A3) all the dates of the month entered, formatting as "D/M/YYYY" (e.g. 1/1/2012, 2/1/2012/ 3/1/2012, etc.)?
I would like to Column E to VLOOKUP the prices in Column B that correspond to the year and month (January in this case) in Column D. I tried to do a VLOOKUP(DATE...) but just couldn't get it....
View 9 Replies View RelatedSee attached spreadsheet. I have a set of Jobs with a set of end dates. A meeting date should be held after the end of each job. I need a formula or macro to determine when the meeting date will be held? The criteria to determine the meeting date is found in rows 12-18. The end dates are set on different days of the weeks and different weeks of the month depending on the region that the job is located in. The meeting dates will be input in colums d, f, h & j.
View 7 Replies View Relatedi need to populate a field to show the month and year as follows: the data is entered in one field like this: 01/09/2006. i need another field to populate this: Jun 06. i have tried to use a few formulae but to avail. look forward to your response
View 4 Replies View RelatedI have a column with dates populated(examp.Fri, 15 Feb 08)in it. In the next column I need to return the month and year (month,year format) this specific date falls in?
View 9 Replies View RelatedI have a large excel file with the top seven rows such as you see in the attached file. In row five there is a bunch of info before the month and the year. What I want to do is get rid of that info besides the month and year. I than want to sort each column by the month and year so that all the columns are in sequential order.
demo.xlsx
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).
I'm needing a formula that will determine the number of days that fall in a specific month based on a date range. For example, if I have a date range of 10/15/2009 to 01/13/2009, I need the formula to determine the number of days in each month within the range (October has 15 days in the date range; November has 30, December has 31, and January has 13.) I have a large spreadsheet that would be so much easier to manage with such a formula. Currently, my spreadsheet is setup as follows. I need the forumla automatically fill in the number of days under each month.
Stard Date End Date Oct-09 Nov-09 Jan-10 Feb-10
10/15/2009 01/13/2009
I'm using Excel 2007.
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 RelatedSerial 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
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 RelatedIn 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 RelatedI'm trying to match certain transactions between two bank accounts, where the only like values are going to be date and disbursement/deposit amount. Obviously vlookup will not work here because there are many duplicate values. In the attachment I put in a Vlookup in the Account 2 tab to try and demonstrate what I'm trying to do exactly, which is match deposits in Account 2 with Disbursements in Account 1.
After looking around I found that maybe a combination of INDEX/MATCH would work here but how to get it to work or even if it is appropriate in this situation.
formula to tell me 9 values. These values are on 9 separate tabs/worksheets. They are always at the bottom row in a specific column - but the row changes every week. I have a totals tab in this workbook. I would like to stop manually going to each tab and pressing Control+End to see the total and manually going back to the totals tab to enter the value. Is there a formula to find these 9 values automatically and display them on my totals tab?
View 6 Replies View RelatedThere 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)
I want to be able to count the number of days in a specific year between two dates.
Suggested formula input: DaysInYear(Date1,Date2,Year)
Examples:
DaysInYear(3/3/2005,3/3/2006,2006) should return 62 (31 in Jan, 28 in Feb and 3 in Mar.)
DaysInYear(3/3/2005,3/3/2007,2006) should return 365
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 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))
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 Relatedi 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 RelatedIm 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 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.
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 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 RelatedI am trying to calculate the number of months in a specific year between two dates.
For example.
Start date 01/06/2012
End Date 01/02/2013
Number of months in 2012 = 6
Number of months in 2013 = 2
How can I write a formula to give me the answer of 6 & 2 from the start and finish dates?
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 RelatedI 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.
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