Hi,

I have this formula which checks whether a date (G$1) is in a range and
performs a bunch of actions based on whether it is or not.

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

The second part starting (IF(TEXT(G$1, "yyymmm" etc is supposed to return a
value ($F2) if the month and year part of G$1 and $K2 match. THis is not
happening
however and for F$2 to be pulled through the day part of the date has to
match also.

This is not supposed to happen! - i only want to match the month and year!

Can anybody help?

Rather than using the TEXT function, try the MONTH and YEAR functions.

=IF(AND(G$1=data!$K2,G$1<=data!$H2),(IF(AND(MONTH (G$1)=MONTH(data!$K2),YEAR(G$1)=YEAR(data!$K2)),$F 2,data!$I2/data!$G2)),"")

HTH,
Elkar


"RGB" wrote:

Hi,

I have this formula which checks whether a date (G$1) is in a range and
performs a bunch of actions based on whether it is or not.

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

The second part starting (IF(TEXT(G$1, "yyymmm" etc is supposed to return a
value ($F2) if the month and year part of G$1 and $K2 match. THis is not
happening
however and for F$2 to be pulled through the day part of the date has to
match also.

This is not supposed to happen! - i only want to match the month and year!

Can anybody help?