Special Date Formula Help needed

[KentKHI]

I have been assigned a task to help with scheduling, and need help on a formula. Below is a 2 lists of dates that I must be able to verify with a true or false answer for the following condition. If any of the Dates in Date List 1 match or are within the same calendar week as any of the dates in Date List 2 I need a "true" response. If the dates in both lists have no matches and none fall within the same calendar week I then need a false response. I cannot seem to figure out how to do it. Any help would be very appreciated!! Thanks!

Book1
	A	B	C	D
1
2	DateList1	DateList2
3	7/14/2008	8/1/2008
4	7/18/2008	8/6/2008
5	7/20/2008	8/8/2008
6	7/19/2008	8/12/2008
7	7/13/2008	8/19/2008
8	7/21/2008	8/3/2008
9	7/29/2008	8/9/2008
10
11
12
Sheet1

 

[Gerald Higgins]

Hi Kent.

I would break this down into separate pieces if I were you.

First of all, check if any of the individual dates match.
You can do this with a VLOOKUP.
For example,

=vlookup(a3,b3:b9,1,false)

or, if you want to trap for errors, which I would recommend in this situation, perhaps something like . . .

=if(isna(vlookup(a3,b3:b9,1,false)),"NO EXACT DATE MATCH","EXACT DATE MATCH")
 
Then, for the weeks, one way would be to apply a week number to each date, for example Dec 31st 2007 to Jan 6th 2008 would all be week 1, Jan 7th to Jan 13th would all be week 2, and so on.
Then, create another version of the VLOOKUP formula that checks these week numbers.

 

[Titian]

Mine is perhaps a low-tec solution but here it is.

In column C3 enter =weeknum(A3,1) and in D3 enter =weeknum(B3,1) and copy down

Compare the two week numbers, if they are the same the dates are in the same week. So in E3 enter =if(C3=D3,"True","False") and copy down.

I think that you will need the "Tools /Add-Ins.../ Analysis Toolpak" checked for the "weeknum" formula to work.

 

[KentKHI]

Titian:

I hadn't heard of the =weeknum formula! that will be the ticket! Thanks!

I guess I did not explain myself very clearly though, I would like to compare the columns as groups, so in one formula I would like to look at all the dates in the first list compared to all the dates in the second list. Obviously this will mean a few nested formulas. Or is this even possible?

 

[Titian]

KentKHI,

If I have interpreted your requirement correctly maybe this will take you some way to your goal.

Developing what we have so far, in "F3" enter "=COUNTIF($C$3:$C$9,D3)", amend the range to suit, and the returned number will indicate how many times the weeknum of the date in "B3" appears in the list of the weeknum of the dates in column A.

 

[KentKHI]

Thanks for all the help guys! I have taken this and worked out my problem! You have made my life a lot easier!