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.

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


"prog" wrote:

> 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.
>