Number of months (elapsed months) between two dates

Good afternoon all,

I am trying to find a formula to calculate the difference between two dates (contract start and end date) in months. I have tried 4 different options and none of them consistently returns the expected result.

To keep it simple assume two scenarios, a contract starting 01/01/2012 and ending 31/12/2012 for which I expect the result to be 12 and a contract starting 15/06/2012 and ending 14/06/2013 for which I also expect the result to be 12.

Option 1: =(EndDate-StartDate)/30 No good because 30 is an imprecise divisor.
Option 2: =DATEDIF(StartDate;EndDate;”M”) No good because it only returns the number of full months so 11 each time. If you +1 to the formula it works for these dates but not for example for a start date of 01/01/2012 and end date of 01/01/2013 which ends up 13.
Option 3: =(YEAR(EndDate)-YEAR(StartDate))*12+MONTH(EndDate)-MONTH(StartDate) This produces the correct result for a start/end date within a month, i.e. 15/06/2012 and 14/06/2013 but not where the start date is in January and end date in December as above.
Option 4: =YEARFRAC(StartDate;EndDate;Basis)*12 This produces the correct result for the January/December example but when the start/end date is within a month it returns a value that is just under or just over 12.

I have researched and researched and not come up with anything else. Have I missed something?

Thanks for any advice you can offer.

If I'm following that correctly, you had +1 in the wrong part of the formula.

You need to add 1 to the end date, not the formula result.

=DATEDIF(StartDate;EndDate+1;”M”)

@ jason.B75..it seems the formula will work correctly..

Well, the problem is - how long is a month? It can be 28, 29, 30 or 31 days in length, and because of this variation you can also get variable results. As you indicated at the end of Option 1, 30 is an imprecise divisor (a better one would be 365.25/12, or 30.4375, but this will not always produce the correct answers if you are counting calendar months).

If your contract goes from 31/01/2012 to 29/02/2012 (29 days) is this one complete month? One would expect so, but if it goes from 31/01/2013 to 01/03/2013, (also 29 days) then what would be the answer here?

How long is a piece of string ?

Pete