how to calculate the number of months between two dates?

[bigdan]

I have a number of dates in a column. In each case I'd like to see how many months between the dates in that column, and another date, say Oct 31. I don't care about the number of days, just number of months.

So if I see a date like May 1, I'd like to see the difference between that and Oct 31 in months ie 6.

How would I do that?

 

[Special-K99]

=DATEDIF(A1,B1,"m")

maybe add 1 to the result

 

[bigdan]

Thanks! Will this be consistent whether the dates are the beginning or end of the month? Ie I'd like the diff b/w 5/1 and 10/31 to be 6 months but 5/31 to 10/31 be 5 months.

When I tried your formula as is it gave me 5 mths as the diff b/w 5/1 and 10/31 and again 5 mths as the diff b/w 5/31 and 10/31. I tried adding 1 to the formula ( so I made it DATEDIF(A1,B1,"m") +1 ) but that gave unexpected results. First it gave me the answer in a date format. When I changed the format back to numbers it gave me 6 as the result (for both 5/1 and 5/31).

What did seem to work is when I modified the formula to become DATEDIF(A1,B1 +1,"m") ie adding the 1 in the second argument. That seems to work! But I'm only saying that on a small sample set. Would the logic be consistent? I don't really follow it.

 

[Special-K99]

I'm not sure, do a Google for DATEDIF and see what it says.
I believe this is an undocumented feature of Excel so information may be sparse.