Month Deduct Formula needed

K

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.
 
P

pietbom via OfficeKB.com

K said:
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
(e-mail address removed)
 
P

pietbom via OfficeKB.com

pietbom said:
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
(e-mail address removed)
in english: JAAR = YEAR, MAAND=MONTH
 
K

K

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
 
N

norie

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top