Counting Continuous Days within overlapping Date Ranges

Hello.

I have three sets of dates indicating prior insurance coverage (most recent listed first):

Coverage 1 Start
Coverage 1 End
Coverage 2 Start
Coverage 2 End
Coverage 3 Start
Coverage 3 End

I need to calculate the total days coveraged for each range (e.g. Total Days for Coverage 1, Total Days for Coverage 2). But I need to count any overlapping days as 1 day.

If coverage 2 extends into coverage 1, then for coverage 1 I will only need to know the number of days after Coverage 2 ends

For example (All dates are inclusive):

Coverage 1 Start 5/1/11
Coverage 1 End 8/31/11
Coverage 2 Start 3/1/11
Coverage 2 End 6/30/11

Coverage 1 Total is 111
Coverage 2 Total is 122
Total between the two is 172
But I want Coverage 1 ( after accounting for overlap to be 50


I hope I explained that well and appreciate any help.

Mark

If I understand correctly you need the number of days between the lowest oldest and most recent days ( date range from A1 to A4)

If calendar days try =max(a1:a4)-min(a1:a4) gives 183
Working days =networkdays(min(a1:a4),max(a1:a4)) gives 132

As you have not indicated your location I suppose you use mm/dd/yyyy format

Don't see where the 172 comes from

That would work, but there are many times when there may be gaps between the end of one and start of another. I appologize that I forgot to mention that.

You are correct about the date format, sorry for not being more specific.

The 172 was just to clarify, I suppose I should've just left it out.


I think I figured it out, at least for part of it....

=IF(AND(Cov1Start>Cov2Start,Cov1Start>Cov2End),Cov1End-Cov1Start+1,IF(AND(AND(Cov1Start>=Cov2Start,Cov1Start<=Cov2End),Cov1End>Cov2End),Cov1End-(Cov2End+1)+1,IF(AND(AND(Cov1Start>=Cov2Start,Cov1Start<=Cov2End),AND(Cov1End>=Cov2Start,Cov1End<=Cov2End)),0,IF(AND(AND(Cov1Start<Cov2Start,Cov1Start<Cov2End),AND(Cov1End>Cov2Start,Cov1End>=Cov2End)),Cov1End-Cov1Start+1,IF(AND(AND(Cov1Start<Cov2Start,Cov1Start<Cov2End),AND(Cov1End>=Cov2Start,Cov1End<=Cov2End)),"Put Dates In Correct Order",IF(AND(Cov2Start>Cov1Start,Cov2Start>Cov1End,Cov2End>Cov1Start,Cov2End>Cov1End),"Put Dates In Correct Order"))))))

After working on this some more, I realize I will probably have to do this through VBA due to too many conditions and criteria. Any suggestions will be appreciated.