If AND OR between two sets of Dates

[sandwichgirl]

Hi

I have a spreadie that has a different primary use, but I would like to use its data to create a separate pivot, but need to set up some filtering criteria first.
Within the columns of raw data I have a week ending date which I would like to use as part of an if/and/or formula in a new column. On another table I have the dates that I would like to use as the parameters for the formula.

I would like my formula to say something like:

If the date in table1columnX is between dateparameter1 or dateparameter2, then 26wks 2011 or if it is between dateparameter3 and dateparameter4, then 26wks 2010

I am using excel 2003 and got as far as:
=IF(AND(AA23956>=datetable!$I$5,AA23956<=datetable!$J$5)),"XX","") and this works but I can't get the second half of the question right.

Any advice or help is appreciated.

many thanks in anticipation

Lou

 

[Jonmo1]

Try like this

=IF(OR(AND(AA23956>=A,AA23956<=B),AND(AA23956>=C,AA23956<=D)),"XX","")

 

[sandwichgirl]

Thank you for the rapid response, however I need the formula if to give one of two answers.

For example if it matches between dates A and B it should give one answer i.e. 26wks2010 or if it matches between C and D then 26wks2011.

cheers

Lou

 

[Jonmo1]

OK,

Try
=IF(AND(AA23956>=A,AA23956<=C),26wks2010,IF(AND(AA23956>=C,AA23956<=D),26wks2011))

 

[sandwichgirl]

Sorted

many thanks indeed.

xx