Matching month part of date only

[RGB]

Hi

I am trying to write a formula which checks whether a specific date is
within a date range. I only need to match the month & year part of the date,
not the exact date.

i have this formula;

=IF(AND(C$1>=A3,C$1<=B3),"yes","no")

an example of the kind of the kind of data i need it for looks like this;

e.g does the start/end date range fall into the specific months along the
top of the rows (jan, feb, mar etc)? - "Yes", or "no" (These dates are in UK
format!)

start date end date jan feb mar apr may jun
jul.....
22/2/6 1/5/6 no yes yes yes yes no
no
14/2/6 21/6/6 no yes yes yes yes yes
no

Only the month and year need to match. the day isnt important

Many thanks for your help

[SteveG]

RGB,

There's probably a simpler formula but this worked for me.

=IF(AND(YEAR(C1)>=YEAR($A$2),MONTH(C1)>=MONTH($A$2),YEAR(C1)<=YEAR($B$2),MONTH(C1)<=MONTH($B$2)),"yes","no")

HTH

Steve

[excelent]

if column C = jan, and column D = feb and so on
=IF(AND(MONTH($A2)<=KOLUMN()-2;MONTH($B2)>=KOLUMN()-2);"yes";"no")


"RGB" skrev:

> Hi
>
> I am trying to write a formula which checks whether a specific date is
> within a date range. I only need to match the month & year part of the date,
> not the exact date.
>
> i have this formula;
>
> =IF(AND(C$1>=A3,C$1<=B3),"yes","no")
>
> an example of the kind of the kind of data i need it for looks like this;
>
> e.g does the start/end date range fall into the specific months along the
> top of the rows (jan, feb, mar etc)? - "Yes", or "no" (These dates are in UK
> format!)
>
> start date end date jan feb mar apr may jun
> jul.....
> 22/2/6 1/5/6 no yes yes yes yes no
> no
> 14/2/6 21/6/6 no yes yes yes yes yes
> no
>
> Only the month and year need to match. the day isnt important
>
> Many thanks for your help
>
>

[RGB]

This formula works really well so long as the date range is within one year.
It seems to fall over when the range lasts longer than one year however..

=IF(AND(YEAR(C1)>=YEAR($A$2),MONTH(C1)>=MONTH($A$2),YEAR(C1)<=YEAR($B$2),MONTH(C1)<=MONTH($B$2)),"yes","no")

For example the range Jul 2006 - Aug 2007 results in "yes" for July 2006 &
August 2006, and yes again for July & August 2007. I need it to say "yes" for
every month in between.

e.g

start date end date jan feb mar apr may jun
jul.....
22/7/6 1/8/7 no no no no no
no yes
14/2/6 21/6/6 no yes yes yes yes yes
no

Cheers

"SteveG" wrote:

>
> RGB,
>
> There's probably a simpler formula but this worked for me.
>
> =IF(AND(YEAR(C1)>=YEAR($A$2),MONTH(C1)>=MONTH($A$2),YEAR(C1)<=YEAR($B$2),MONTH(C1)<=MONTH($B$2)),"yes","no")
>
> HTH
>
> Steve
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=561559
>
>

[SteveG]

RGB,

The problem is the MONTH formula will not always be true for the months in the preceeding years. You can use the day part of the date (make excel change the day to the first since the actual date is not important just the month).

=IF(AND(C1>=DATE(YEAR($A$2),MONTH($A$2),1),C1<=DATE(YEAR($B$2),MONTH($B$2),1)),"yes","no")

This makes the date in A2 to = the 1st of July 2006 and in B2 the 1st of August 2007.


HTH

Steve