I 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
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?
I have been struggling for almost 2 days with this problem.
I have a list of data, one column of which is the date displayed in dd/mmm/yy format. This date will always be entered by users and is variable.
I can't find a way of counting the number of occurences of each month and each day that the date represents.
So, I want to know how many occurences of March, June, May, Septemeber etc are on this list and Mondays, Tuesdays, etc.
I have tried several posssible routes, DCOUNT DCOUNTA COUNT COUNTIF SUM. I've also tried separating the date out into days & months using the MONTH & DAY functions but this didn't work either.
It also apepars (shock horror) that Excel has incorrect date & day values because entering todays date in one cell (11/05/2006) and then using the DAY function to find the day of the week for this date produces WED when it should be THU. (My system date is set as 1 Jan 1900).
I feel that Excel can't separate the months & days away from the date, since the date is stored as a numerical value and not as we humans use dates. The dates will always be manually entered by users, probably as dd-mm and Excel will automatically add in the year.
How can I count the occurences of each month and days of the week?
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
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.
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...
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"
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.
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 tried everything I know (which isnt that much to be honest. lol). Ive tried the frequency formula but that doesn't work the way I want it - I think its probably the wrong formula to use. I've also tried a pivot table but they always vex me. If a pivot table IS the way to go, could someone talk me through it step by step? (*the wizard is just as confusing as doing it yourself I find) ....
I have a spreadsheet for tracking jobs. Most everything is based off of week # rather than date. I am trying to get the stats page of the workbook to tally the total number of late jobs per week.The current week is taken care of because there is a function that automatically displays on time yes or no and I just set it to count the yeses or nos.The problem I am having is for past weeks.
I tried- =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<Weeknum(Now())" but that doesn't work. I also tried isolating the < like this. =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<"Weeknum(Now()) and that did not work either.
------ UPDATE
In response to using CountIFS I have also tried-
=COUNTIFS(Table2[On-time],"NO",Table2[Due Week '# Hidden],"<Weeknum(Now())") this just returns a zero value even when I have a late job listed three weeks ago.
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)
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.
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
