comparing a date range within another date range

[Guest]

Hi,

What I'm trying to do is create a program that loops through a small set of
dates that I have in column D and E (start dates and end dates). So, it
takes these line by line and compares them to another larger set of dates in
column A and B. The result is to show if any of the dates from Column D and
E fall in between the dates of column A and B. Furthermore, each group of
dates has a unique number id. So, the data consists of dates that are
grouped by number IDs.
Example:

Group 1 Group 2
id start_date end_date id start_date end_date
-----------------------------
----------------------------------
1 6-6-07 6-7-07 1 6-6-07 6-10-07
1 6-9-07 6-11-07 2 etc etc
1 6-13-07 6-15-07 3 etc etc
2 etc etc
3 etc etc

So I need a way to take group 2's date values and compare to see if they
fall between the correct group id for Group 1's dates. Any ideas would be
great. Thanks.

 

[Guest]

You need to show either Group 2 start date or end date falls within one of
the Group 1 ranges. You can have any one of the following conditions

1) Group 2 dates falls outside Group 1 dates
2) Group 2 dates fall inside the Group 1 dates
3) Group 2 date starts before Group 2 date but ends in the midde of Group 2
dates
4) Group 2 date starts in the middle of Group 1 dates but ends after Group 1
5) Group 1 dates start before Group 2 dates and end after Group 2 dates


Sub checkdates()


LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set Group1Range = Range(Cells(1, "A"), Cells(LastRow, "A"))

LastRow = Cells(Rows.Count, "D").End(xlUp).Row
Set Group2Range = Range(Cells(1, "D"), Cells(LastRow, "D"))


For Each Group2cell In Group2Range

If IsDate(Group2cell) Then

For Each Group1cell In Group1Range

If IsDate(Group1cell) Then

If (((Group2cell >= Group1cell) And _
(Group2cell <= Cells(Group1cell.Row, "B"))) Or _
((Cells(Group2cell.Row, "E") >= Group1cell) And _
(Cells(Group2cell.Row, "E") <= Cells(Group1cell.Row, "B"))))
Or _
((Group2cell <= Group1cell) And _
(Cells(Group2cell.Row, "E") >= Cells(Group1cell.Row, "B")))
Then

'enter your code when both conditions are met


End If

End If

Next Group1cell

End If

Next Group2cell

End Sub