Matching month part of date only

[Guest]

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

 

[Guest]

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:

 

[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

Stev

 

[Guest]

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]

RGB,

The problem is the MONTH formula will not always be true for the month
in the preceeding years. You can use the day part of the date (mak
excel change the day to the first since the actual date is no
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 1s
of August 2007.


HTH

Stev