Matching the month and year portion of a date only

[Guest]

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?

 

[Guest]

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