Formula for number of months between 2 dates

[Mike]

other than the Datedif function which only calculated
complete months between 2 dates, is there a more accurate
formula for calculating the number of months between 2
dates?

 

[J.E. McGimpsey]

What would you consider "more accurate"?

Months is a slippery concept - they range from 28 days to 31 (or
more) days.

Would 31 January 2004 to 29 February 2004 be 1.0 months? or 29/31
months?

Would 28 February 2003 to 29 February2004 be 12.0 months? Or 12 +
1/28th months? Or 12 + 1/29th months?

Is 31 January 2003 to 30 April 2003 3.0 Months? 2 + 30/31sts months?
3 + 1/30ths months?

Is 3 months after 28 February 2003 to be 28 May 2003? 31 May 2003?

Unless you give your precise definition of "month" it's impossible
to know what will be "accurate" to you.

 

[John Drummond]

You do need to be very careful monthly differences, to be sure, however, two
Excel functions that can be useful are DAYS360 and YEARFRAC. I think
DAYS360/360 would give not a bad answer a lot of the time.

 

[J.E. McGimpsey]

I tend to agree, but you'd need to use Days360()/30, and
YearFrac*12, right?

 

[Guest]

Since I am dealing with a billing period of one month
increments, the preciseness has to be to the 100th of a
month accuracy. i.e. 5.15 months.

Calculations need to be inclusive of the dates specified.
Ie. Jan 1 to Jan 31 is 1.0 months. Jan 1 to feb 2 would
be 1 month + 2/28 or 1.36 months.

 

[John Drummond]

I was afraid you'd say that. That's a kind of ACTM/12 daycount, very
logical for what you are doing. Try:

=(YEAR(G15+1)-YEAR(F15))*12+MONTH(G15+1)-MONTH(F15)+(DAY(G15+1)-1)/DAY(EOMON
TH(G15+1,0))-(DAY(F15)-1)/DAY(EOMONTH(F15,0))