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.