Intersection of two time intervals

[mikskov]

Hello all,

I was wondering if there exists a neat effective way of calculating the number of days two date intervals overlaps?

Example:
Interval 1: 5. jun 2011 - 21. jun 2011
Interval 2: 10. jun 2011 - 15. jun 2011
Interval 3: 1. jan 2011 - 30. sep 2011

Intersection( interval 1, interval 2 ) = 6 days
Intersection( interval 1, interval 3 ) = 17 days

Many thanks!

 

[jbeaucaire]

Excel Workbook
	A	B	C	D
1	Start	End
2	6/5/2011	6/21/2011
3	6/10/2011	6/15/2011	6
4	1/1/2011	9/30/2011	17
5
Sample Sheet

 

[Peter_SSs]

If you are always checking the overlap with Interval 1, is this what you mean?

Formula copied down.

Excel Workbook
	A	B	C
1
2	05-Jun-2011	21-Jun-2011
3	10-Jun-2011	15-Jun-2011	6
4	01-Jan-2011	30-Sep-2011	17
5	25-Jun-2011	08-Jul-2011	0
6	21-Jun-2011	22-Jun-2011	1
7	01-Jan-2011	28-Feb-2011	0
Overlap