count overlapping days given two ranges

[crlim]

I want to count the number of overlapping days given a start and end date and a range:
For example,

Start Date /1/2004 and End Date of 4/15/2004
Range 1 start 1/1/2004
Range 1 end 3/31/2004

Range 2 start 4/1/2004
Range 2 end 6/30/2004

How can i count the number of days within my start to end date overlap with each range????
So in this case # of days within Range 1 = 91 days and within Range 2 = 16 days
Any help is much appreciated!

 

[IML]

with the previous formula given, put your start and stop dates in b1 and b2. Use e1 and e2 for start and stop of range 1 (yeilds 91 days) or range 2 ( yields 15, not 16 days).

Am I missing something here?

 

[mdusoe]

How about this:

Book1
	A	B	C	D	E
1		Start	End	NumberofDays
2	Range1	1/1/2004	3/31/2004	60
3	Range2	4/1/2004	6/30/2004	15
4
5		StartDate	2/1/2004
6		EndDate	4/15/2004
7
8
Sheet3

Formula in Cell D2 is:
=SUMPRODUCT(--(ROW(INDIRECT(B2&":"&C2))>=$C$5)*--(ROW(INDIRECT(B2&":"&C2))<=$C$6))

Please note, this will only work with "Range Dates" before June 5, 2079. After that, you run out of Rows for the function to use...

HTH,
Mike.

 

[IML]

For comparitive purposes, using this same set up

=MAX(0,MIN($C$6,C2)-MAX($C$5,B2)+1)
in cell d2 and copied down to d3 yields the same results. I'm still not sure how this differs from your earlier post

 

[mdusoe]

IML,

I was trying to get there, but I was making the logic too difficult, and confusing myself. That formula is, of course, superior to mine.

And I thought I was losing my mind when you said something about his previous post in your first reply here. I was like "What Previous Post?!?!?"

Oh well... need sleep... kids kept me up all night.... G'night now... ZZZzzz...

(I wish )
Mike.