Months Between 2 dates

[bronsonelliott]

I'm trying to calculate the months between 2 dates similar to the Oracle 'MONTHS_BETWEEN' function (MONTHS_BETWEEN)

I've seen a few examples in the forums similar to this:

=Num#(Year(Date1)&Month(Date1))-Num#(Year(Date2)&Month(Date2))

However, this isn't performing the way I'm needing.  For example, if I feed it a start date of 1/15/2012 and an end date of 2/14/2012, it returns a value of '1'.  I would expect it to return 0.

Here's what I'm trying to accomplish, if the start date is 1/15/2012 and the end date is 2/14/2012, the months between would be zero.  However, if the dates are 1/15/2012 and 2/15/2012, then the months between would be 1.

I hope this makes sense.  Any help is appreciated.

Thanks!

[#2]

Hi Elliott

Doi you want just generate dates for a calendar table or do you need to feed blanks between 2 dates but many times in the same table?

Best regards

Chris

[#3 · Accepted solution]

Hi,

A possible way:

= (year(Date2)-year(Date1))*12 + num(month(Date2)) - num(month(Date1)) + (day(Date2)<day(Date1))

Counting the number of monthes and eventually adding a boolean of -1.

[#4]

Hi Bronson Elliott,

You can try this.

  Floor(num(BigggerDate-SmallerDate)/30,1,1)

This gives you the number of months between two dates. One month is taken as 30 Days. If you need to check some exceptional dates condition, you can add in if statement.

[bronsonelliott]

Thank you Pierre, this seems to be working like I'm needing it too.