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.

K wrote:
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.

I solved your problem:

Formula: '=(JAAR($A$1)*12+MAAND($A$1))-(JAAR(D1)*12+MAAND(D1))
You can use this formula in the conditional format
k.r.
Piet Bom


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

pietbom wrote:
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
[quoted text clipped - 23 lines]
hilight rows in which dates are six months old from current date.
Please can any friend help me.

I solved your problem:

Formula: '=(JAAR($A$1)*12+MAAND($A$1))-(JAAR(D1)*12+MAAND(D1))
You can use this formula in the conditional format
k.r.
Piet Bom

in english: JAAR = YEAR, MAAND=MONTH

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

thanks for replying piethbom. What is "JAAR" and "MAAND" as i am using
excel 2007 and i cant find these formulas. Can you please help

You could try something like this but it does give slightly different
results than you expect.

=IF($A$1B1,DATEDIF(B1,$A$1,"m"),DATEDIF($A$1,B1," m"))

On Jun 20, 7:27*am, K wrote:
thanks for replying piethbom. What is "JAAR" and "MAAND" as i am using
excel 2007 and i cant find these formulas. *Can you please help