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

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,

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)),"ye s","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

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)),"ye s","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)),"ye s","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

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<=DAT E(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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=561559