Special Date Formula Help needed

KentKHI

Active Member
Joined
Oct 1, 2004
Messages
492
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
ABCD
1
2DateList1DateList2
37/14/20088/1/2008
47/18/20088/6/2008
57/20/20088/8/2008
67/19/20088/12/2008
77/13/20088/19/2008
87/21/20088/3/2008
97/29/20088/9/2008
10
11
12
Sheet1
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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,
Code:
=vlookup(a3,b3:b9,1,false)

or, if you want to trap for errors, which I would recommend in this situation, perhaps something like . . .
Code:
=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

Well-known Member
Joined
Dec 17, 2004
Messages
567
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.
 
Last edited:

KentKHI

Active Member
Joined
Oct 1, 2004
Messages
492
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

Well-known Member
Joined
Dec 17, 2004
Messages
567
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

Active Member
Joined
Oct 1, 2004
Messages
492
Thanks for all the help guys! I have taken this and worked out my problem! You have made my life a lot easier!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,841
Messages
5,598,390
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top