Number of months between 2 dates

Question

I am trying to create a formula or VBA function which calculates the number of months between two dates, according to this rule: if the start date is the 15th of the month or before, or the end date is after the 15th of the month, then that month counts.

For example:

Start Date   End Date   Output
----------   ---------  --------
1/5/2014     2/16/2014  2 months
1/17/2014    2/16/2014  1 month
1/16/2014    2/5/2014   0 months

I have already tried =DATEDIF(A2, B2, "M") + IF( DATEDIF(A2, B2, "MD")>=15, 1, 0) but this only adds a month if the distance between the days in 2 dates is over 15. For example if the start date is 5/14/13-8/16/13 it will say that there are 3 months in between these dates. However, the actual distance between these 2 dates should be 4 months according to the conditions that I specified above.

Ho do I fix this formula?

@Jean-FrançoisCorbett see the edit to my post above. =MONTH() will not work since the since I cannot measure against the conditions I mentioned above. — user3653819
The point was, please show what you tried -- and yes, MONTH can be used as you see in the answer you accepted. Another point: 1/14/2014 to 2/16/2014 should return 2 months? Are you sure this is what you want? — Jean-François Corbett

engineersmnky engineersmnky · Accepted Answer · 2014-05-19T20:51:57

Here is a vba solution as well

Function date_diff_to_months(date1 As Date, date2 As Date) As Integer


   Dim y1 As Integer
   Dim y2 As Integer
   Dim d1 As Integer
   Dim d2 As Integer
   Dim m1 As Integer
   Dim m2 As Integer
   Dim m_diff As Integer
   Dim y_diff As Integer
   Dim month_adjustment As Integer
 
   y1 = Year(date1)
   y2 = Year(date2)
   m1 = Month(date1)
   m2 = Month(date2)
   d1 = Day(date1)
   d2 = Day(date2)
 
   m_diff = m2 - m1
   y_diff = (y2 - y1) * 12
 
   If (m_diff > 0 Or y_diff > 0) Then
       If (d1 <= 15 And d2 >= 15) Then
          month_adjustment = 1
       ElseIf (d1 >= 15 And d2 <= 15) Then
          month_adjustment = -1
       End If
   End If
 
   date_diff_to_months = m_diff + y_diff + month_adjustment
End Function

Number of months between 2 dates

2 Answers