hello,

I have the following formula which works fine;

=IF(AND(L$1=data!$K2, L$1<=data!$H2),
(IF(MONTH(L$1)=MONTH(data!$K2),$F2,data!$I2/data!$G2)),"")

I need to change it so that the bit (IF(MONTH(L$1)=MONTH(data!$K2) checks
that a date matches not only the month but the year also (i dont need to
match the day).

I tried this; - but excel says that it contains an error!

=IF(AND(L$1=data!$K2, L$1<=data!$H2), (IF(MONTH(L$1),(YEAR
(L$1))=(MONTH(data!$K2)),(YEAR (L$1)),$F2,data!$I2/data!$G2)),"")

Any ideas how I can correct (IF(MONTH(L$1),(YEAR
(L$1))=(MONTH(data!$K2)),(YEAR (L$1)) to work?

many thanks

rgb

Hi

You'll need an AND in there. Try this:
=IF(AND(L$1=data!$K2, L$1<=data!$H2),
(IF(AND(MONTH(L$1)=MONTH(data!$K2),YEAR(L$1)=YEAR( data!$K2)),$F2,data!$I2/data!$G2)),"")
untested.

Andy.

"RGB" wrote in message
...
hello,

I have the following formula which works fine;

=IF(AND(L$1=data!$K2, L$1<=data!$H2),
(IF(MONTH(L$1)=MONTH(data!$K2),$F2,data!$I2/data!$G2)),"")

I need to change it so that the bit (IF(MONTH(L$1)=MONTH(data!$K2) checks
that a date matches not only the month but the year also (i dont need to
match the day).

I tried this; - but excel says that it contains an error!

=IF(AND(L$1=data!$K2, L$1<=data!$H2), (IF(MONTH(L$1),(YEAR
(L$1))=(MONTH(data!$K2)),(YEAR (L$1)),$F2,data!$I2/data!$G2)),"")

Any ideas how I can correct (IF(MONTH(L$1),(YEAR
(L$1))=(MONTH(data!$K2)),(YEAR (L$1)) to work?

many thanks

rgb

You may use the AND function there, as you did before
=IF(AND(L$1=data!$K2, L$1<=data!$H2),
IF(AND(MONTH(L$1)=MONTH(data!$K2),YEAR(L$1)=YEAR(d ata!$K2)),$F2,data!$I2/data!$G2),"")

Hope this helps,
Miguel.

"RGB" wrote:

hello,

I have the following formula which works fine;

=IF(AND(L$1=data!$K2, L$1<=data!$H2),
(IF(MONTH(L$1)=MONTH(data!$K2),$F2,data!$I2/data!$G2)),"")

I need to change it so that the bit (IF(MONTH(L$1)=MONTH(data!$K2) checks
that a date matches not only the month but the year also (i dont need to
match the day).

I tried this; - but excel says that it contains an error!

=IF(AND(L$1=data!$K2, L$1<=data!$H2), (IF(MONTH(L$1),(YEAR
(L$1))=(MONTH(data!$K2)),(YEAR (L$1)),$F2,data!$I2/data!$G2)),"")

Any ideas how I can correct (IF(MONTH(L$1),(YEAR
(L$1))=(MONTH(data!$K2)),(YEAR (L$1)) to work?

many thanks

rgb

=IF(AND(L$1=data!$K2, L$1<=data!$H2),
(IF(TEXT(L$1,"yyyymm")=TEXT(data!$K2,"yyyymm"),$F2 ,data!$I2/data!$G2)),"")

--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

"RGB" wrote in message
...
hello,

I have the following formula which works fine;

=IF(AND(L$1=data!$K2, L$1<=data!$H2),
(IF(MONTH(L$1)=MONTH(data!$K2),$F2,data!$I2/data!$G2)),"")

I need to change it so that the bit (IF(MONTH(L$1)=MONTH(data!$K2) checks
that a date matches not only the month but the year also (i dont need to
match the day).

I tried this; - but excel says that it contains an error!

=IF(AND(L$1=data!$K2, L$1<=data!$H2), (IF(MONTH(L$1),(YEAR
(L$1))=(MONTH(data!$K2)),(YEAR (L$1)),$F2,data!$I2/data!$G2)),"")

Any ideas how I can correct (IF(MONTH(L$1),(YEAR
(L$1))=(MONTH(data!$K2)),(YEAR (L$1)) to work?

many thanks

rgb

Many thanks Miguel,

The formula still seems to have to exactly match the day part of $K2 to pull
through $F2 however. I need to match the year and month parts of $K2 only.

Cheers

RGB


"Miguel Zapico" wrote:

You may use the AND function there, as you did before
=IF(AND(L$1=data!$K2, L$1<=data!$H2),
IF(AND(MONTH(L$1)=MONTH(data!$K2),YEAR(L$1)=YEAR(d ata!$K2)),$F2,data!$I2/data!$G2),"")

Hope this helps,
Miguel.

"RGB" wrote:

hello,

I have the following formula which works fine;

=IF(AND(L$1=data!$K2, L$1<=data!$H2),
(IF(MONTH(L$1)=MONTH(data!$K2),$F2,data!$I2/data!$G2)),"")

I need to change it so that the bit (IF(MONTH(L$1)=MONTH(data!$K2) checks
that a date matches not only the month but the year also (i dont need to
match the day).

I tried this; - but excel says that it contains an error!

=IF(AND(L$1=data!$K2, L$1<=data!$H2), (IF(MONTH(L$1),(YEAR
(L$1))=(MONTH(data!$K2)),(YEAR (L$1)),$F2,data!$I2/data!$G2)),"")

Any ideas how I can correct (IF(MONTH(L$1),(YEAR
(L$1))=(MONTH(data!$K2)),(YEAR (L$1)) to work?

many thanks

rgb