Count days in specific range based on criteria

Hi -

Attached is your file with the following formula added:

=MAX(IF(VLOOKUP($K$2,$A$2:$E$1178,5,0)=0,VLOOKUP($K$2,$A$2:$E$1178,4,0)-H4,I4-VLOOKUP($K$2,$A$2:$E$1178,5,0)),0)

I used Cell K2 to enter whatever property number you want and the formula finds that property and determines the days vacant in cells C4:K8 by each month. First, the formula looks to see if there is a move out date. If not, then it compares the move in date to the beginning of the month (Column H). If there is a move out date, it compares that date to the end of the month (Column I). The MAX function gets rid of any negative numbers than arise when there is no Move Out date and we are looking into future months.

Attached is your Spreadsheet with the above formula in place.

Hope this helps.

Hi -

I see the problem. You don't want the total cumulative days the unit has been empty. You just want the days of that month that it's been empty. Try this:

=MAX(IF(VLOOKUP(G4,A:E,5,0)=0,MIN(VLOOKUP(G4,A:E,4,0)-H4,I4-H4+1),MIN(I4-VLOOKUP(G4,A:E,5,0),I4-H4+1)),0)

Paste into K4 and copy down. Let me know if you have any other issues.

The upload is showing move-outs way into the future ... including Evictions??

I'm not certain that I am interpreting this correctly.

With the 1st of the month dates as helpers in F1:I1 this helper formula entered in $F$2:$I$1178.

Formula:

Please Login or Register  to view this content.

Then this summary formula in M4:M7

Formula:

Please Login or Register  to view this content.

	J	K	L	M
3				Days vacant
4	6/1/2016	6/30/2016	June	88
5	7/1/2016	7/31/2016	July	97
6	8/1/2016	8/31/2016	August	81
7	9/1/2016	9/30/2016	September	52

Hi -

OK I can fix that. But on the properties that have multiple entries/tenants, is there ever a time where you have a gap between those tenants or are they both living there so the building is considered occupied from the time the first tenant moves in until the last tenant move out?

Example:

Tenant 1 move in 6/1/16 moves out 7/15/16
Tenant 2 move in 7/31/16 move out 8/31/16

Is the above considered occupied from 6/1/16 through 8/31/16? Or do you consider there to be a 16 day gap from 7/15 to 7/31?

Hi login...that's exactly what I'm looking for, the specific number of days a property is vacant between tenants. In your example, I do consider there to be a gap between 7/15 and 7/31, although technically I would count 15 vacant days (when working on a similar issue, I found https://exceljet.net/formula/calcula...verlap-in-days, where it analogizes days between dates to fence posts). That is, we count the move-out date as still occupied.

Flameretired I'll look at yours in just a minute, that appears to be what I'm looking for.

With all the work you guys are putting into this (which I'm tremendously grateful for!), I almost feel I should have saved you all the trouble and just gone with VBA. But thank you all.