Fast way to determine months difference of two dates

[#1]

Hi,

What would be the fastest way to determine the difference of two dates in terme of months.

YYYY/MM/DD

date1 : 2008/01/01
date2 : 2010/04/01

month difference would be = 27

date1 : 2010/01/01
date2 : 2010/12/01

month difference would be = 11

Hope you can help me. tnx in advance.

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

Something like this might work

=Round(interval(date('01/04/2010') - date('01/01/2008')) / 30)

Do you need to measure fractions of months? (i. e.: three months and a half)

[#3]

I think this will tnx for the help, nope i don't need to compute for fraction.

[johnw]

That's basically what I'd do, but I wouldn't use 30. If your dates are far enough apart, you can get the wrong result. Instead, use the average number of days in a month, which I believe is 30.436875.

[disqr_rm]

Try this:

((YEAR(date2)-YEAR(date1))*12)+(MONTH(date2)-MONTH(date1))

Should give you correct results no matter how far the dates are.

Please test and post your findings here, if possible, so others can take benefit of this as well. Thanks.

[#6]

To avoid rounding inaccuracy, you could try:

12*(year(date2)-year(date1)) + (month(date2)-month(date1))

[johnw]

Rakesh Mehta wrote:Should give you correct results no matter how far the dates are.

It depends on what results you consider correct. For instance, how many months are there between July 1, 2010 and August 31, 2010? One month or two? The year and month approach says one. The round((date1-date2)/30.436875) approach says two. EITHER could be considered correct. It depends on what you're after.

[#8]

Tested this and it worked fine. Many thanks Rakesh.

Cheers,

Griff