FORMULA NEEDED FOR MONTHS
 

Hi,

There's no formula that I know of that will cater for what you are looking
for. What you can use is a VBA function to calculate the month difference.
Try something like the below function (paste the below into a module)

'To use on an Excel worksheet type =Months(A1,D1) where A1 represents the
first date
'and D1 represents the second date

Public Function Months(Date1 As Date, Date2 As Date) As Long

Dim iMonthCount As Long, sYear As Long, eYear As Long, sMonth As Long,
eMonth As Long

sMonth = Month(Date1)
eMonth = Month(Date2)
sYear = Year(Date1)
eYear = Year(Date2)

If sMonth = eMonth Then
iMonthCount = Abs(sMonth - eMonth)
If sYear eYear Then
iMonthCount = ((sYear - eYear) * 12) + iMonthCount
ElseIf sYear < eYear Then
iMonthCount = ((Abs(sYear - eYear)) * 12) - iMonthCount
End If
ElseIf eMonth sMonth Then
iMonthCount = Abs(eMonth - sMonth)
If sYear eYear Then
iMonthCount = ((sYear - eYear) * 12) - iMonthCount
ElseIf sYear < eYear Then
iMonthCount = ((Abs(sYear - eYear)) * 12) + iMonthCount
End If
End If

Months = iMonthCount

End Function

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200906/1

FORMULA NEEDED FOR MONTHS
 

No need for the VBA code. There is an undocumented function in Excel
called DATEDIF that will calculate the interval between two dates in a
variety of intervals. E.g.,

=DATEDIF(StartDate,EndDate,"m")

This is not to be confused with the VBA DateDiff function. For more
info about DATEDIF, see www.cpearson.com/Excel/DateDif.aspx

For reasons known only to Microsoft, they don't document this rather
useful function.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Sat, 20 Jun 2009 13:28:48 GMT, "Alan McQ via OfficeKB.com"
<u22393@uwe wrote:

Hi,

There's no formula that I know of that will cater for what you are looking
for. What you can use is a VBA function to calculate the month difference.
Try something like the below function (paste the below into a module)

'To use on an Excel worksheet type =Months(A1,D1) where A1 represents the
first date
'and D1 represents the second date

Public Function Months(Date1 As Date, Date2 As Date) As Long

Dim iMonthCount As Long, sYear As Long, eYear As Long, sMonth As Long,
eMonth As Long

sMonth = Month(Date1)
eMonth = Month(Date2)
sYear = Year(Date1)
eYear = Year(Date2)

If sMonth = eMonth Then
iMonthCount = Abs(sMonth - eMonth)
If sYear eYear Then
iMonthCount = ((sYear - eYear) * 12) + iMonthCount
ElseIf sYear < eYear Then
iMonthCount = ((Abs(sYear - eYear)) * 12) - iMonthCount
End If
ElseIf eMonth sMonth Then
iMonthCount = Abs(eMonth - sMonth)
If sYear eYear Then
iMonthCount = ((sYear - eYear) * 12) - iMonthCount
ElseIf sYear < eYear Then
iMonthCount = ((Abs(sYear - eYear)) * 12) + iMonthCount
End If
End If

Months = iMonthCount

End Function