Calculate the number of overlapping days?

[mishmish3000]

Good morning all!
OK, here's a fairly interesting question. Background: we need to be able to calculate the number of overlapping days between Inmate1 and Inmate2 in a prison, so we can see how long Inmate1 was exposed to an infectious disease Inmate2 happened to have.
I figured out how to use NETWORKDAYS and got an answer, but that only shows working days. In prison, one does not have a work week and then a weekend. It's all the same in The Big House, man.
So, my question is: help me write an Excel formula that produces the number of overlapping days, not depending on whether it's during the week or on the weekend--all days--between two inmates. I'll attach a spreadsheet with an example.
Gee, I'm usually asked questions about babies with hepatitis B or kids with whooping cough (pertussis). I'm not used to questions about prison! LOL
Any help would be appreciated!
MishMish3000

[agibsonsw]

Probably just :

=IF(B3<=C2,C2-B3+1,0)

You need the IF in case there is no overlap. Andy.

[HansV]

Try this:

=MAX(MIN(C2:C3)-MAX(B2:B3)+1,0)

MAX(B2:B3) is the later of the two entrance dates.
MIN(C2:C3) is the earlier of the two exit dates.
MIN(C2:C3)-MAX(B2:B3) is the number of days between those.
The +1 adds one so that both entrance date and exit date are included. (If you don't want that, you can omit the +1).
If there is no overlap, the result of MIN(C2:C3)-MAX(B2:B3) is negative. Taking MAX(...,) ensures that the final result is 0 in that case.

[mishmish3000]

Wow, cool! I experimented with Andy's formula and found it seemed to work more logically if I did this:
=IF(B3<=C2,C3-B3+1,0)

But then I saw Hans' approach and did some experiments with it, using different date sets for our happy Inmate1 and Inmate2.

So I GREATLY appreciate everyone's input. Here's my example, with experiments included.

Thanks!!!
MishMish3000

[agibsonsw]

You really needs Hans' answer. This would cover all possibilities.

[mishmish3000]

This will be SO great!! My coworker (who asked the question this morning) just stopped by to say THANKS!
So thanks, guys!

MishMish3000

oh, you know... it snowed 3-5 inches in Nashville TN last Wednesday afternoon. My usual commute home takes about an hour (I live out in the woods, away from Nashville). It took... get ready... 7.5 hours to get home last Wednesday! People had abandoned their cars, were sliding off the road and into each other, or were sitting in gridlock for hours. People here in the US South do NOT understand how to deal with snow.

[mishmish3000]

OK, here's a twist on the question... what if we want to be able to copy the formula down, but have it compare, say, Inmate1 to Inmate2, then Inmate1 to Inmate3... etc. We have over 500 prisoners and we want to see if they had overlapping stays with the inmate in question, our friend Inmate1. I've attached the spreadsheet with a few more notes...
MishMish3000

[HansV]

In your sample workbook: enter the following formula in E28:

=MAX(MIN(C$27,C28)-MAX(B$27,B28)+1,0)

and fill down as far ar needed. The $ before 27 makes the row number 27 absolute instead of relative, meaning that Excel will not adjust it when you fill down the formula. If you look at E29 after filling down, its formula is

=MAX(MIN(C$27,C29)-MAX(B$27,B29)+1,0)

As you can see, C$27 and B$27 have remained the same, while C28 has become C29 and B28 has become B29.

[mishmish3000]

Yes... it works! I should have remembered relative and absolute references. Duh! Many, many thanks!