Terry said:
I am trying to count days in the Database that overlap each other?
An example would be:
12Jan05 - 15FEB05
20Jan05 - 09Feb05
08Jan05 - 31Jan05
The answer would be 3 because atleast one number in each group overlaps the
other.
Any help would be greatly appreciated.
Thank You
Terry
The following function should help:
'Begin Module Code-----
Public Function DateIntersection(dt1 As Date, dt2 As Date, dt3 As Date,
dt4 As Date) As Integer
'Return the number of days overlapping two date ranges
'Assumes dt1 <= dt2 and dt3 <= dt4 Ranges can go in either order
DateIntersection = 0
If dt2 <= dt3 Then
If dt2 = dt3 Then DateIntersection = 1
Exit Function
End If
If dt4 <= dt1 Then
If dt4 = dt1 Then DateIntersection = 1
Exit Function
End If
If dt1 <= dt3 And dt3 <= dt2 And dt2 <= dt4 Then
DateIntersection = DateDiff("d", dt3, dt2) + 1
Exit Function
End If
If dt1 <= dt3 And dt3 <= dt4 And dt4 <= dt2 Then
DateIntersection = DateDiff("d", dt3, dt4) + 1
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt2 And dt2 <= dt4 Then
DateIntersection = DateDiff("d", dt1, dt2) + 1
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt4 And dt4 <= dt2 Then
DateIntersection = DateDiff("d", dt1, dt4) + 1
End If
End Function
'End Module Code-------
Then if you want to know how many of the date ranges overlap:
Abs(DateIntersection(CDate("12 Jan 05"), CDate("15 FEB 05"), CDate("20
Jan 05"), CDate("09 Feb 05")) > 0) +
Abs(DateIntersection(CDate("12 Jan 05"), CDate("15 FEB 05"), CDate("08
Jan 05"), CDate("31 Jan 05")) > 0) +
Abs(DateIntersection(CDate("20 Jan 05"), CDate("09 Feb 05"), CDate("08
Jan 05"), CDate("31 Jan 05")) > 0)
= 3
To get from "08Jan05" to "08 Jan 05" try:
Left("08Jan05", 2) & " " & Mid("08Jan05", 3, 3) & " " & Right("08Jan05", 2)
Hopefully this will get you started.
James A. Fortune
(e-mail address removed)
