Overlapping Date Ranges

[Jose]

Hi,

I have 4 columns, 2 start date and 2 end dates. In most cases, both are the
same. In some, the start date is the same and the end date is different and
vice versa, how can I accurately get the number of days overlap?

 

[Douglas J. Steele]

Your signature talks about Excel help, but you've posted to a newsgroup
related to Access, the database product that's part of Office Professional.

If your question really is about Excel, you'd be best off reposting to a
newsgroup related to Excel.

If it's an Access question, what exactly do you mean by "the number of days
overlap"?

You can calculate the number of days between a given pair of start and end
dates using DateDiff("d", [Start], [End])

If what you're trying to do is get the difference between the later of the
two start dates and the earlier of the two end dates, try

DateDiff("d", IIf([Start1] < [Start2], [Start2], [Start1]), IIf([End1] <
[End2], [End1], [End2]))

 

[James A. Fortune]

Hi,

I have 4 columns, 2 start date and 2 end dates. In most cases, both are the
same. In some, the start date is the same and the end date is different and
vice versa, how can I accurately get the number of days overlap?

Someone must have been wondering where I was. Try:

http://groups.google.com/group/comp.databases.ms-access/msg/9df7500e40e39155

It should work in an Excel Macro also. Something like (air code):

Dim Wks As Worksheet
Dim lngLastRow As Long
Dim lngI As Long

Set Wks = ActiveSheet
With Wks
lngLastRow = .Cells(.Rows.Count, "A".End(xlUp).Row
End With
For lngI = 2 To lngLastRow 'Headings on row 1
With wks
If Not IsEmpty(.Cells(lngI, "A").Value) And Not
IsEmpty(.Cells(lngI, "B").Value) And Not IsEmpty(.Cells(lngI,
"C").Value) And Not IsEmpty(.Cells(lngI, "D").Value) Then
If IsDate(.Cells(lngI, "A").Value) And IsDate(.Cells(lngI,
"B").Value) And IsDate(.Cells(lngI, "C").Value) And IsDate(.Cells(lngI,
"D").Value) Then
.Cells(lngI, "E").Value = DateIntersection(CDate(.Cells(lngI,
"A").Value), CDate(.Cells(lngI, "B").Value), CDate(.Cells(lngI,
"C").Value), CDate(.Cells(lngI, "D").Value))
End If
End If
End With
Next lngI

'You can put DateIntersection code here

Columns A through D should be formatted as dates. The macro will put
the number of days of overlap in column E. This example assumes that
columns A and B contain a start and end date and that columns C and D
contain another start and end date. You'll probably have to edit out
any carriage returns caused by line wrap.

James A. Fortune
(e-mail address removed)

 

[gllincoln]

Hi Jose,

Interesting puzzle - is a VBA Function solution okay?

I ran some randomly generated dates through this and I think this function may meet your stated objective. I haven't methodically tested it with every possible combination.

I'm not happy with this solution - it's ugly, not the least bit elegant - sheer brute force. I'd like to see how a different approach might clean it up - maybe a strong programmer lurking about will give this a whirl and show me how I shoulda coulda done it better. This is similar to a lot of scheduling type programming challenges so it's a worthwhile knot to untangle in a reusable, more extensible manner.


'sd1 = start one
'sd2 = start two
'ed1 = end one
'ed2 = end two

Function myOverLap(ByVal sd1 As Date, ByVal sd2 As Date, ByVal ed1 As Date, ByVal ed2 As Date) As Integer
'same start
If sd1 = sd2 Then
'same finish
If ed1 = ed2 Then
'complete overlap, calc the days
myOverLap = ed1 - sd1 + 1
Else
'same start two ends first
If ed1 > ed2 Then
myOverLap = (ed2 - sd2) + 1
Else
'same start one ends first
myOverLap = (ed1 - sd1) + 1
End If
End If
Else
'different start one before two
If sd1 < sd2 Then
'one before two same end
If ed1 = ed2 Then
myOverLap = (ed1 - sd2) + 1
Else
'one ends before two starts
If sd2 > ed1 Then
myOverLap = 0
Else
'one starts first - one ends first
If ed1 < ed2 Then
myOverLap = (ed1 - sd2) + 1
Else
'one starts first - two ends first
myOverLap = (ed2 - sd2) + 1
End If
End If
End If
Else
'two starts first - same end
If ed1 = ed2 Then
myOverLap = (ed1 - sd1) + 1
'one starts after two ends
ElseIf sd1 > ed2 Then
myOverLap = 0
Else
'two starts first - one ends first
If ed1 < ed2 Then
myOverLap = (ed1 - sd1) + 1
Else
'two starts first - two ends first
myOverLap = (ed2 - sd1) + 1

End If
End If
End If
End If

End Function


==================


Hope this helps...
Gordon