Matching the month and year portion of a date only

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)),$F2,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?