Intersecting dates

[transkawa]

How do I do this?
If I have a range of dates, say, 9/1/2010 - 9/29/2010 and then another
range, 9/5/2010 - 9/30/2010. I want to be able to check for intersection
in both range of dates. If an intersection exist, be able to calculate:
1. the number of days for which the intersection exist.
2. the start date and end dates for the intersections.
I wish anyone can help me resolve this issue.
TIA

--
two things i learnt from life:
the hardest things are the simplest of all; they just take time
the simple things are not hard at all
So I just try to KISS it.
http://emekadavid-solvingit.blogspot.com

 

[Rick Rothstein]

You didn't say how your ranges are "stored", so I assumed the following...

A1: Start date for first range (9/1/2010)
B1: End date for first range (9/29/2010)
A2: Start date for first range (9/52010)
B2: End date for first range (9/30/2010)

Given that arrangement, you can use this code to get the values you asked
for...

Dim FirstDateRange As Range, SecondDateRange As Range, Overlap As Range
Set FirstDateRange = Range(CLng(Range("A1").Value) & ":" &
CLng(Range("B1").Value))
Set SecondDateRange = Range(CLng(Range("A2").Value) & ":" &
CLng(Range("B2").Value))
Set Overlap = Intersect(FirstDateRange, SecondDateRange)
If Not Overlap Is Nothing Then
MsgBox "Number of days in overlap: " & Overlap.Rows.Count & vbLf & _
"Start date for overlap: " & CDate(Overlap(1).Row) & vbLf & _
"End date for overlap: " & CDate(Overlap(Overlap.Count).Row)
Else
MsgBox "Those date ranges do not overlap!"
End If

Note that all I am doing here is converting each of the dates to their
numerical offset from "date zero", and then treating those converted numbers
as Excel row numbers, creating Excel cell ranges from them and then
intersecting them.

 

[Rick Rothstein]

You might also find this blog article, and the comments posted to it, of
some use...

http://chandoo.org/wp/2010/07/07/da...g+-+Learn+Excel+&+Charting+Online+»+Featured)

 

[joeu2004]

If I have a range of dates, say, 9/1/2010 - 9/29/2010 and then another
range, 9/5/2010 - 9/30/2010. I want to be able to check for intersection
in both range of dates. If an intersection exist, be able to calculate:
1. the number of days for which the intersection exist.
2. the start date and end dates for the intersections.

If the first range is in A1:B1 and the second range is in A2:B2, then
put #2 into A3:B3 as follows:

A3: =IF(MIN(B1:B2)-MAX(A1:A2)<0,"",MAX(A1:A2))
B3: =IF(A3="","",MIN(B1:B2))

And put #1 into A4 as follows:

=IF(A3="","",B3-A3+1)

 

[joeu2004]

A3:  =IF(MIN(B1:B2)-MAX(A1:A2)<0,"",MAX(A1:A2))

Which can be simplified, namely:

=IF(MIN(B1:B2)<MAX(A1:A2),"",MAX(A1:A2))

PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

 

[transkawa]

You didn't say how your ranges are "stored", so I assumed the following...

A1: Start date for first range (9/1/2010)
B1: End date for first range (9/29/2010)
A2: Start date for first range (9/52010)
B2: End date for first range (9/30/2010)

Given that arrangement, you can use this code to get the values you asked
for...

Dim FirstDateRange As Range, SecondDateRange As Range, Overlap As Range
Set FirstDateRange = Range(CLng(Range("A1").Value) & ":" &
CLng(Range("B1").Value))
Set SecondDateRange = Range(CLng(Range("A2").Value) & ":" &
CLng(Range("B2").Value))
Set Overlap = Intersect(FirstDateRange, SecondDateRange)
If Not Overlap Is Nothing Then
MsgBox "Number of days in overlap: " & Overlap.Rows.Count & vbLf & _
"Start date for overlap: " & CDate(Overlap(1).Row) & vbLf & _
"End date for overlap: " & CDate(Overlap(Overlap.Count).Row)
Else
MsgBox "Those date ranges do not overlap!"
End If

Note that all I am doing here is converting each of the dates to their
numerical offset from "date zero", and then treating those converted numbers
as Excel row numbers, creating Excel cell ranges from them and then
intersecting them.

is your Dims and Sets VBA or a macro?
Newbie to Excel really. Just about a month old.
TIA

 

[Rick Rothstein]

Not sure of your question here... macros are made from VBA, they are not
independent from it. You would put the code I posted inside your macro's
body (that is, inside a Sub/End Sub blocking). The reason I did not do so is
because I wasn't sure how you ultimately wanted to use the code (I assumed
you were going to incorporate this in some way inside an existing macro you
had). To see the code work, here it is as a stand-alone, self-contained
macro...

Sub ShowOverlapEtc()
Dim FirstDateRange As Range, SecondDateRange As Range, Overlap As Range
Set FirstDateRange = Range(CLng(Range("A1").Value) & _
":" & CLng(Range("B1").Value))
Set SecondDateRange = Range(CLng(Range("A2").Value) & _
":" & CLng(Range("B2").Value))
Set Overlap = Intersect(FirstDateRange, SecondDateRange)
If Not Overlap Is Nothing Then
MsgBox "Number of days in overlap: " & Overlap.Rows.Count & vbLf & _
"Start date for overlap: " & CDate(Overlap(1).Row) & vbLf & _
"End date for overlap: " & CDate(Overlap(Overlap.Count).Row)
Else
MsgBox "Those date ranges do not overlap!"
End If
End Sub