the summary sheet only keeps two months of data and i use the below code to
to find out where the cutoff month is.
but of course now that it is january it results in an error because we are subtracting 1-2.
i was going to set up a Select statement to check if the month is 1 or 2 and deal with it that way.
strOldMonth = MonthName(Month(Selection) - 2)
I have a drop down box where I can choose 6 different metals, and another drop down box to choose different months in the future. I have the current price of each of these metals, but they change for each different month in the future - I also have how much the current price should change by for each of these separate months. I currently have a cell using a simple IF statement to choose the current price for each metal, however I'd like this cell to also be able to change for the specific different month in the future. This is roughly how it looks:
Expiry Jun-12
Product Copper
Future PriceXXXX
I need a formula that will calucalte the monthly total based on the following conditions:
Col A = Yearly Cost
Col B = The number of the month when the costs are starting (1=Jan etc)
Col C = The duration or the number of months for which the costs are to spread
Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.
Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx
In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.
First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.
What I am trying to do is take the dates in Column B and count each date as a month, and put those totals into the January through December boxes. I've put in the amounts that should be shown with the data I provided.
View 2 Replies View RelatedI am trying to calculate the number of months between two given dates.
The spreadsheet is basically about staff and their start and end dates.
For example start dates would be in colum A and end dates would be in column B (in dd/mm/yyyy format).
I am currently using the following formula:
=DATEDIF(A1,B1,"m")
It does return the correct results when the dates are the same e.g. start date = 01/01/2006 and end date = 01/04/2006 = 3 months.
BUT when I change the end date to 31/03/2006, it returns as 2 months.
Is there a way around this? I need a more accurate number of months (with no decimal places)
I have an excel list with 3 columns (date, comment, cost):
I'm continously adding new entries to the list and want to see the average cost per month without remembering how many different months I already entered.
For this I need the number of months extraced from the date-column so I can devide the cost total by the numer of months.
The date format is dd.mm.yy
i have column A and B with list of dates. Column A has earlier dates when compared to column B.
i am trying to find a formula with which i can know the difference between the dates in "number of months".
Ex: Column A has 06/01/2014 and Column B has 08/30/2014. The difference between the dates in terms of month is 3 which i need in a formula.
I have a problem again with one of Date Dif function " =DATEDIF(I5|J5|"m") " in attached file i have this function applied to calculate number of months in between two given dates but it seems this function is not working properly or i am missing some info in this to make it run properly. How to fix this function or give me a proper one which can solve my problem to calculate accurate months between these dates in attached file...
Excel Date problem.xlsx
I have a row containing:
Date entered
Name
Date of Birth (DOB)
Occurrences of Name + DOB this Month
Occurrences of Name + DOB within last 3 Months
Occurrences of Name + DOB within last 6 Months
I can manage the occurrences this month but haven't a clue how to calculate the 3 and 6 months ones. I have attached a sheet.
Start date: 12/04/2004
End date: 12/04/2006
The formula should give the answer to 24 months
Example 2
Start date: 12/04/2004
End date: 13/04/2006
The formula should give the answer to 25 months
When I use function =(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3), it does not
show 25 months for "Example 2" as it is still within the same month "April"
I need to automatically calculate the number of months a deal runs through 2012 dependant on the start and end date.
I have attached a basic spreadsheet. Column C shows the results I would like the formula to calculate.
i have first date in cell a1 and second date in cell b1. I want formula to to how many months are in between these two dates,, for example
first date: 1 jan 12
swcond date: 5 apr 12
result: 4
Is there a formula which would show the number of months between two dates eg.
15-Jan-06 to 30-Nov-07 = 23 months
I'm using the Calendar Control to determine 2 dates. 1 Starting Date and 1 Ending Date. I'm trying to determine the total number of months between those two dates down to 2 Decimal Places. I also need to get the total number of weeks and days but those are much easier and I've been able to work those out by the following:
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes" ........
I want to calculate leave on the basis of Date of joining (DOJ). Rule is:
If DOJ is not he first day of the month, then no leave is earned for that month. Suppose DOJ is 26/12/2006, for Dec.2006 no leave is earned. One leave will be earned in January 2007 & subsequent months.
But if DOJ is 01/12/2006, then one leave will be earned in Dec.2006 & subsequent months also.
A zip file containg two excel files is attached for ready reference.
I've developed a spreadsheet in Excel 2000 where one of the cells contains the number of months a quantity will last, calculated by taking the total amount and dividing it by the amount used each month (a constant).
I would like to have another cell where that number is translated into the future date that number of months represents, so that in addition to seeing how many months the total amount will last, one can see when the amount will be depleted.
For example, suppose it is December 9, 2009 (as it is now) and the number in the number-of-months cell is 6. Then, is there a way that when 6 is calculated, in another cell appears something like June 9 2010 or 6/9/2010 or 6/9/10 (assuming the computer's clock is correct)?
It would be nice if the formula would handle decimals too, such as the date for 6.4 months, but that's not essential.
I need a simply excel formula or macro which calculates the numbers of months i need to accrue for certain costs. The data I have includes the start month of the invoice, the end month of the invoice (i.e. showing how long the invoice is relevant for, quite often 1 year), and obviously the current month.
(eg. a 12 month invoice with a start date of Oct 08, end date Sep 09, current May 09 = 8 mths to accrue)
(eg2. a 12 month invoice with a start date of Apr 09, end date of Mar 10, current May 09 = 2 mths to accrue)
(eg3. a 12 month invoice with a start date of Jun 09, end date of May 10, current May 09 = 0 mths to accrue)
Have been doing some sums on excel. I've managed to calculate the number of months between 2 dates using this formula.....
=DATEDIF(C3,D3,"y")&" years "&DATEDIF(C3,D3,"ym")&" months "
I then come up with a list of months calculated.
What I now want to know is, how many are equal to or more than 9 months? And if this is the case then can the cells be highlighted in a different colour?
if A1 is a start date and B1 is the end date, I want C1 to return the number of months there are between the start and end dates?
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 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.
Just wondering if there is a formula where I can change the date an employee started working into the number of months employed??
Example, John Smith started here in January 2011. I would like the formula to return 8 months.
Is there a simple function for calculating the number of months between 2 dates.
Eg 1/5/2012 - 8/9/2020
I have a "start date" and an "end date". Is there any way to tell how many months are included in 2007 from those two dates using a formula? For instance, from 01-Feb-04 to 01-Feb-07, there is 1 month in 2007 (January). And likewise, 01-Jan-05 to 01-Jan-08 there is 12 months in 2007.
As noted, I have the start and end dates. I don't have the "how many months in 2007".
Start Date End Date How many months in 2007
01-Feb-0401-Feb-07 1
01-Jan-0301-Jan-06 0
01-Apr-0401-Apr-07 3
01-Feb-0301-Feb-06 0
01-Mar-0401-Mar-07 2
01-Feb-0401-Feb-07 1
01-Dec-0401-Dec-07 11
01-Jan-05 01-Jan-08 12
The formula contained within these cells equals =MONTH(C84) [the date 2 cells above it]... to which I've formatted the cell to "mmmm" and it displays "January" across the board. Can anyone explain why this is occuring and how I can fix it please? I'm aiming to graph this information and can't accurately do it using the moving monthly system i've put in place if I can't change the months dynamically like this.
Secondly, and on a side note. Line 84 currently takes todays date (not currently todays date in the example screenshot) (=TODAY()), and to get to the previous 13 months I deduct 31 days. I can foresee this as being a problem further down the track especially when leap years come into play. Is there a function in Excel where I can simply deduct 1 month from another? =MONTH(C84)-1 ?
I'm trying to write a function that takes a date in format YYYYMM and adds/subtracts a number of months.
for example: newDate(YYYYMM,numMonths)
newDate(201109,5) would give 201202
newDate(201102,-4) would give 201010
I was thinking i could use the left/right function to retrieve the month and year ........
I have a date column (Column E: Date Entered) on my spreadsheet that I need to set conditional formatting on. There are two conditions:
1) 18 months from the date in the cell needs to be highlighted yellow
2) 24 months from the date in the cell needs to be highlighted red
Cell A1 is a past date. In cell B1 I would like how many months have gone by since todays date. eg. Cell A1 = July 07, B1 would = 24 months.
View 3 Replies View RelatedI am looking for a formula that will add months and return the year. E.g. if I add 1.05 and 1.07 I should get 3.01. i.e. 3 years and 1 month.
View 9 Replies View Related