Difference between two months

L

leitek.com

Hi there,

I am trying to get a difference between two months, I looked every
where in excel date functions and on excel groups to no avail.

I came across the datedif function but for some reason this function
works very strange. lets say I have a date in cell A1 as 12/31/2005 and
the date in cell B1 is 2/1/2006. now the difference in months should be
2 and not 1 as this function indicates. when I use 12/1/2005 as my
start date then I get 2 as an answer, not sure why the inconsistency.

this is the current formula --> DATEDIF(A1,B1,"m")

I realize that 12/31 is the end of the month for december and therfore
it is caluclating the month difference between 12/31 and 2/1 as one
month. but I want the difference in months, so if I have 12/1 or 12/31
as my starting date and my ending date is 2/1 or 2/28, I still want the
difference to show as 2.

I would appreciate any feedback/input for this problem..

thanks a lot...
 
P

Pete_UK

If you subtract two dates then you will get the number of days between
them, but, how many days are in a month? Do you take it as 365/12 ? If
so, then this formula might give you what you want:

=INT((A1 - B1 + 365/12)*12/365)

Hope this helps.

Pete
 
N

Niek Otten

=(YEAR(B1)*12+MONTH(B1))-(YEAR(A1)*12+MONTH(A1))

Format as Number, not as Date (which Excel will do automatically)
 
Top