Excel function that returns difference between month?

5
votes

I have a worksheet that has a start date and end date. I need to get the number of months between these dates. I used the formula =MONTH(B1)-MONTH(A1) to get that #. However this formula fails when my dates span multiple years - if start date is 1/1/2014 and end date is 1/1/2015, then it returns a "1", when I'd like to get a "12".

I then tried DATEDIF(A1,B1,"m") to get the # of months between the dates, and that worked. My issue, however, can be summarized by the following example:

Situation 1:

Start Date: 1/5/2014

End Date: 3/1/2014

"MonthDif": 2

DateDif: 1

Situation 2:

Start Date: 1/5/2014

End Date: 3/10/2014

"MonthDif": 2

DateDif: 2

As you can see, the DATEDIF function returns the # of complete months, while my month - month function returns the number of months "occupied" by the difference between the start and end dates, regardless if they are complete months.

I need the number of months, regardless if full months, over any date period! Basically, I need exactly what the MONTH(B1)-MONTH(A1) returns, except it will work over multiple years.

Also, I was considering designing a custom VBA function to achieve the above. If anyone has any suggestions there.

3
excelvbafunction

3 Answers

5
votes

If you use DATEDIF but always count from the 1st of the first month you'll get what you need, e.g. if you use =A1-DAY(A1)+1 that will give you the first of the A1 month, so use that in DATEDIF like this

=DATEDIF(A1-DAY(A1)+1,B1,"m")

2
votes

Assuming B1 contains your end date and A1 contains your start date,

=IF(DAY(B1)>=DAY(A1),0,-1)+(YEAR(B1)-YEAR(A1))
*12+MONTH(B1)-MONTH(A1)
1
votes

I need exactly what the MONTH(B1)-MONTH(A1) returns, except it will work over multiple years.

Ok, well, at the risk of stating the obvious:

=MONTH(B1)-MONTH(A1) + 12*(YEAR(B1)-YEAR(A1))

Rationale: a year is composed of twelve months.