FORMULA NEEDED FOR MONTHS

[K]

Hi all, I am trying to create month deduct formula below but not
having any success. In below data I got current date in cell A1 and
various dates in column D and then in column E in cell E1 I have
formula "=Month($A$1)-Month(D1)" which then continue to down. As you
can see in below data in cell E1 i am getting result which is "-5" as
it should be "7" because if you count months from date "19/06/2009"
to
"01/11/2008" they are "7". I am trying to get postive figure in
column E and as you can see there are few results in negative figures
and also incorrect.

A D E -----col
19/06/2009 01/11/2008 -5
12/12/2008 -6
15/03/2009 3
16/10/2009 -4


The result should come something like below


A D E----col
19/06/2009 01/11/2008 7
12/12/2008 6
15/03/2009 3
16/10/2009 4


I need this formula also for conditional formatting as I am tring to
hilight rows in which dates are six months old from current date.
Please can any friend help me.

 

[Alan McQ via OfficeKB.com]

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

 

[Chip Pearson]

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)