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.

 

[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)